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.