problems with query

[M]

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@

[Hugo Kornelis]

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