Upgraded from MS SQL Server 2000 to 2012, Now Problem with Joins

Transact-SQL
1

Hi everybody. I hope I've found the right place to ask this question.

I just upgraded to SQL Server 2012, and I've found that I can no longer use the *= syntax for joining tables. This affected a few dozen queries, all of which I updated to the now required "INNER JOIN / OUTER JOIN" syntax. This worked flawlessly for all but one troublesome query, which is no longer producing the same results that it did when I used the old syntax.

It is my understanding that a "left outer join" means that the item on the left-hand side of the equation can be null. And when I was using *= syntax with SQL Server 2000, that's exactly what happened. I had some rows with null values, and they were included in the results. However, now that I've upgraded to 2012 and updated the syntax, the rows with null values are no longer showing up in the results.

Here is the old query that used to work:

SELECT * FROM Comp_Results, Comp_Scores, Competitors AS Ladies, Competitors AS Gentlemen
WHERE Comp_Results.CompMgr_ID = Comp_Scores.CompMgr_ID
AND Partner1_ID *= Gentlemen.CompMgr_ID
AND Partner2_ID *= Ladies.CompMgr_ID
AND Comp_Scores.CompMgr_ID = 1034
AND Comp_Scores.Comp_Year_ID = 111
AND Comp_Results.Comp_Year_ID = 111
AND Gentlemen.Comp_Year_ID = 111
AND Ladies.Comp_Year_ID = 111
ORDER BY Comp_Scores.CompMgr_ID, Comp_Score_ID;

And here is the updated query:

SELECT * FROM Comp_Results
INNER JOIN Comp_Scores ON Comp_Results.CompMgr_ID = Comp_Scores.CompMgr_ID
LEFT OUTER JOIN Competitors Gentlemen ON Comp_Scores.Partner1_ID = Gentlemen.CompMgr_ID
LEFT OUTER JOIN Competitors Ladies ON Comp_Scores.Partner2_ID = Ladies.CompMgr_ID
WHERE Comp_Scores.CompMgr_ID = 1034
AND Comp_Scores.Comp_Year_ID = 111
AND Comp_Results.Comp_Year_ID = 111
AND Gentlemen.Comp_Year_ID = 111
AND Ladies.Comp_Year_ID = 111
ORDER BY Comp_Scores.CompMgr_ID, Comp_Score_ID;

The rows that I would like to have included are the ones where Partner1_ID and Partner2_ID are both null. They represent the header rows in the table, and don't have competitors' data in them.

Any help would be greatly appreciated.

Regards,
Jonathan Atkinson
www.comporganizer.com

2

Move the logical expressions in the WHERE clause that refer to columns from the RIGHT tables of those LEFT JOINs to the JOIN condition. Like shown below:

SELECT  *
FROM    Comp_Results
INNER JOIN Comp_Scores ON Comp_Results.CompMgr_ID = Comp_Scores.CompMgr_ID
LEFT OUTER JOIN Competitors Gentlemen ON Comp_Scores.Partner1_ID = Gentlemen.CompMgr_ID
			AND Gentlemen.Comp_Year_ID = 111
LEFT OUTER JOIN Competitors Ladies ON Comp_Scores.Partner2_ID = Ladies.CompMgr_ID
			AND Ladies.Comp_Year_ID = 111
WHERE   Comp_Scores.CompMgr_ID = 1034
AND Comp_Scores.Comp_Year_ID = 111
AND Comp_Results.Comp_Year_ID = 111
ORDER BY Comp_Scores.CompMgr_ID ,
Comp_Score_ID;
1 Like