Posted by Hugo Kornelis on 07/12/06 23:09

On 12 Jul 2006 13:42:50 -0700, M@ wrote:

>I am trying to tie two tables of data together and I'm having some
>issues.
>
>table 1 has about 1700 rows
>table 2 has about 1000 rows
>table 3 has about 70
>
>they all have one column in common, provider number.
>
>select a.provider, a.[survey-date], a.tag, a.code, a.[scope-severity],
>a.state, a.facilityname,
>c.prov, c.defic_dt, c.tag_no, c.scopesev, c.theirnam, b.[name]
>from table1 a
>inner join table3 b on b.prov = a.provider
>inner join table2 c on b.prov = c.prov
>
>problem is, I am getting 35000 rows instead of 2700.
>
>What am I missing?
>thanks,
>M@

Hi M@,

Let's say that for a specific provider, there are 6 rows in table 1, 4
rows in table 2 and 5 rows in table 3. Since your join conditions only
specify that the "provider" values should be equal, each of the 6 rows
for this provider will be joined to each of the 5 in table 3, yielding
30 rows in the intermediate result - and each of those 30 will be
combined with each of the 4 rows for this provider in table 2. The end
result will have 4 * 30 = 120 rows for this single provider!

If you need help correcting the query, then you'll have to post more
details: the structure of your tables (as CREATE TABLE statements,
including all constraints, properties and indexes), some well-chosen
rows of sample data (posted as INSERT statements) and the exected
results.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]