user7146708 user7146708 - 1 year ago 117
SQL Question

Number of rows reduced after join

I have a query that gives me 759 rows:


However, when I join the table to get a column from another table, the number of rows is reduced to 707

BuildingTable as A

Inner Join SurveyTable as B
On a.buildingAddress = b.address

What is the best way to test which rows I lost and why? and how do I prevent this from happening? I was thinking that maybe some of the buildings don't have survey costs and therefore it was only showing me the ones that have costs, but I see some null values there so that was not the issue, I think.

If you need extra information let me know. Thanks

Answer Source

To find the rows you have lost, just replace the inner join with a left join and look for missing rows:

Select bt.*
From BuildingTable bt left join
     SurveyTable st
     on bt.buildingAddress = st.address
where st.address is null;

Note that rows can also be duplicated in both tables, so you might have more missing rows than you expect.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download