Sunday, February 19, 2012

Converting T-SQL *= OUTER JOINS to ANSI-92 syntax

Hello all. I have an application that has worked smoothly using the following query syntax:
FROM tbl_participation, tbl_adult, tbl_month, tbl_school_year
WHERE tbl_participation.adult_ID =* tbl_adult.ID

AND tbl_participation.month_ID =* tbl_month.ID
AND tbl_participation.year_id =* tbl_school_year.ID

AND tbl_adult.ID = 8
AND tbl_school_year.ID = 5
It works just fine, as I want the results to include a table with one column containing the month name, the table headed by the adult's name/school year. It needs to still return a table even if there have yet been no records in tbl_participation for that adult/month/year.
However, the following is my best shot at making the query ANSI-92 compliant, as when I implement SQL Server 2005 I don't want to have to go back and change compatibility modes:
FROM ((tbl_adult

LEFT OUTER JOIN tbl_participation ON tbl_participation.adult_ID = tbl_adult.ID)

RIGHT OUTER JOIN tbl_month ON tbl_participation.month_ID = tbl_month.ID)

RIGHT OUTER JOIN tbl_school_year ON tbl_participation.year_id = tbl_school_year.ID

WHERE tbl_adult.ID = 8
AND tbl_school_year.ID = 5
This query works fine if there is any data in tbl_participation for adult 8 and school year 5. But if nothing has yet been entered, it returns nothing. I need it to work like the older T-SQL iteration, and still return a list of the 12 months and the adult's name even if no participation data has been entered.
Thanks - this one has got me pulling my hair out.

With the JOIN clause, you should logically think of the ON clause as being evaluated first followed by WHERE clause, GROUP BY and HAVING clause. Note that with inner joins the optimizer can evaluate predicates in the WHERE and ON clause together. But when you outer joins the WHERE clause is always evaluated after the ON clause. So this will essentially prevent non-matching rows from being produced based on your example. Additionally, it also depends on whether you filter the rows before the outer join. So in your example, you need to move the tbl_adult.ID check to FROM clause using a derived table like:

FROM (( (select * from tbl_adult where ID = 8) as a
LEFT OUTER JOIN tbl_participation as p ON p.adult_ID = a.ID)
RIGHT OUTER JOIN tbl_month as m ON p.month_ID = m.ID)
RIGHT OUTER JOIN tbl_school_year as y ON p.year_id = y.ID
WHERE y.ID = 5

No comments:

Post a Comment