Unexpected Join behavior
in [Programming SQL]
|
From: Mij on 15 Jul 2010 14:01 I have a parent table for inspections, for example: (tblInspection) Inspec_ID Property_ID Inspec_Date Inspec_Occupied 1 349 6/4/2010 Y 2 533 6/7/2010 N I also have a child table, the follow-up inspections: (tblFolUpInspection) Inspec_ID FolUp_ID FolUp_Date 1 1 7/5/2010 1 2 7/9/2010 I am trying the following query: SELECT N.Inspec_ID, N.Inspec_Occupied, F.FolUp_ID FROM dbo.tblInspection N LEFT JOIN dbo.tblFolUpInspection F ON N.Inspec_ID = F.Inspec_ID WHERE N.Inspec_ID = 1 I am expecting to see results with 3 records, one from the original and two from the follow-ups: Inspec_ID Inspec_Occupied FolUp_ID 1 Y NULL 1 Y 1 1 Y 2 but I only see the follow-up records like it was an inner join: Inspec_ID Inspec_Occupied FolUp_ID 1 Y 1 1 Y 2 I've also tried FULL and RIGHT OUTER joins but same results. Why is this working this way, and is there a way to get what I really want? Thanks for any help. Mia J. *** Sent via Developersdex http://www.developersdex.com ***
From: Eric Isaacs on 15 Jul 2010 14:24 You're left joining the tables on Inspection ID, so you're only going to get 1 row if there are no followups or 1 row for each follow-up. If you want all three, you need to do something like this... SELECT N.Inspec_ID, N.Inspec_Occupied, NULL FROM dbo.tblInspection N WHERE N.Inspec_ID = 1 UNION SELECT N.Inspec_ID, N.Inspec_Occupied, F.FolUp_ID FROM dbo.tblInspection N INNER JOIN dbo.tblFolUpInspection F ON N.Inspec_ID = F.Inspec_ID WHERE N.Inspec_ID = 1 -Eric Isaacs
From: Mij on 15 Jul 2010 14:44 Actually, I think I get no rows when there are no follow-ups. I'm not really understanding the join part though; does it have something to do with the fact that the Inspection ID is a full key of one table and a partial key of the other table, that it won't give me a row for the original inspection? Mia J. *** Sent via Developersdex http://www.developersdex.com ***
From: Eric Isaacs on 15 Jul 2010 15:36 > Actually, I think I get no rows when there are no follow-ups. If you INNER JOIN the two tables, you will only get results if there are related records in both tables. If you LEFT JOIN, as you did originally, you will get all rows in the table to the left of the LEFT JOIN syntax and any related rows from the table on the right of the LEFT JOIN syntax. If there are no related rows, you'll still get the original row on the left side. It seems that you want to treat the follow-up inspections as separate inspections. That being the case, I would suggest a different table design. I would instead put the follow-up inspections in the inspection table and add a Parent_InsepctionID column to tblInspection to represent the relationship between primary inspections and follow- up inspections. In that case your SQL would be as follows... SELECT N.Inspec_ID, N.Inspec_Occupied, N.Parent_InspectionID FROM dbo.tblInspection N WHERE N.Inspec_ID = 1 or N.Parent_InspectionID = 1 ....That would yield you all three rows without the need for a UNION. You would also determine if the inspected property was occupied on each follow-up as well. -Eric Isaacs
From: Tom Cooper on 15 Jul 2010 15:50
You don't get no rows when there are no followups, you will get one. The reason you don't see a row from Inspec_ID 2 is the WHERE clause eliminates that row. If you didn't have a WHERE clause, you would see the two row's you are currently getting for Inspec_ID 1 and one row from Inspec_ID 2. The row from Inspec_ID 2 would have NULL in the FolUp_ID column. But I think you have a misunderstanding about how LEFT JOIN's work. If you do a left join and the table on the left has no matching rows uning the join condition, then you get one row with the values from the table on the left and NULLs in the columns from the table on the right. But if the row in the table on the left has one or more matches, then for that row, the join does exactly the same thing as an INNER join. So if when there are matches, you want a row with NULL's in the collumns from the table on the right inaddition to all the matched rows, then you need to do something else. Probably the easiest way to get this is to do a UNION - which allows you to get the results of two queries, merge them together and then eliminates any duplicates, for example SELECT N.Inspec_ID, N.Inspec_Occupied, F.FolUp_ID FROM dbo.tblInspection N LEFT JOIN dbo.tblFolUpInspection F ON N.Inspec_ID = F.Inspec_ID WHERE N.Inspec_ID = 1 UNION SELECT N.Inspec_ID, N.Inspec_Occupied, F.FolUp_ID FROM dbo.tblInspection N LEFT JOIN dbo.tblFolUpInspection F ON 1 = 2 WHERE N.Inspec_ID = 1 ORDER BY Inspec_ID, FolUP_ID; The top half gets the rows you are currently getting, the bottom half gets each row from dbo.tblInspection with NULL's in the columns from dbo.tblFolUpInspection (because 1 is never equal to 2). Then the UNION merges the results. It is, of course, possible that a row from dbo.tblInspection doesn't match a row from dbo.tblFolUpInspection, in which case it will be present twice (once from the top half and once from the bottom half), but since the UNION eliminates duplicates, your output will only show it once. BTW, it's best to show dates as 'YYYYMMDD' both when passing them as string literals to SQL Server and when writing to this group. Much of the world, many people on this newsgroup, and sometimes SQL Server (depending on your options) will look at '7/5/2010' and think May 7, 2010, others will think July 5, 2010. But '20100705' is always July 5 to everyone. It didn't make any difference in this case, but sometimes it does. Tom "Mij" <mdsj(a)infi.net> wrote in message news:uFhV92EJLHA.5432(a)TK2MSFTNGP04.phx.gbl... > Actually, I think I get no rows when there are no follow-ups. I'm not > really understanding the join part though; does it have something to do > with the fact that the Inspection ID is a full key of one table and a > partial key of the other table, that it won't give me a row for the > original inspection? > > Mia J. > > *** Sent via Developersdex http://www.developersdex.com *** |