CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012left-join
ShruS
ShruS

Reputation: 71

SQL Server double left join counts are different

Code: 

Select a.x,
    a.y,
    b.p,
    c.i
    from table1 a left join table2 b on a.z=b.z
    left join table3 on a.z=c.z;

When I am using the above code I am not getting the correct counts:

I am having hard time figuring out why I am getting different counts. According to my understanding I should be getting 30 counts even after the 2nd left join.

Can anyone help me understand this difference?

I am using sql server 2012

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

There are multiple rows in table3 with the same z value.

You can find them by doing:

select z, count(*)
from table3
group by z
having count(*) >= 2
order by count(*) desc;

If you want at most one match, then outer apply can be useful:

Select a.x, a.y, b.p, c.i
from table1 a outer apply
     (select top 1 b.*
      from table2 b
      where a.z = b.z
     ) b outer apply
     (select top 1 c.*
      from table3 c
      where a.z = c.z
     ) c;

Of course, top 1 should be used with order by, but I don't know which row you want. And, this is probably a stop-gap; you should figure out why there are duplicates.

Upvotes: 2

Deadsheep39
Deadsheep39

Reputation: 611

In your table table3 contain more then 1 row per 1 row in table1. Check one value which is occured more times in both tables.

You can use group by with max function to make one to one row.

Upvotes: 1

Related Questions