NULL or no values in OUTER JOINS

[Captain Special]

Here it is, finally. Phil, thanks for your help - you were instrumental in
getting my thinking along the right lines.

SELECT X.*,
tbl_month.ID AS monthID, tbl_month.month_name, tbl_month.fy_order,
tbl_school_year.ID AS syID, tbl_school_year.school_year,
tbl_adult.ID AS adID, tbl_adult.assigned_ID, tbl_adult.last_name,
tbl_adult.first_name, tbl_adult.middle_initial, tbl_adult.ssn,
tbl_adult.family_ID AS famID

FROM
(SELECT * FROM tbl_part_adult
WHERE year_ID = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">
AND adult_ID = <CFQUERYPARAM VALUE="#form.part_ID#"
CFSQLTYPE="CF_SQL_INTEGER">) as X
RIGHT OUTER JOIN tbl_month ON tbl_month.ID = X.month_ID
LEFT OUTER JOIN tbl_school_year ON tbl_school_year.ID = X.year_ID
LEFT OUTER JOIN tbl_adult ON tbl_adult.ID = X.adult_ID

ORDER BY fy_order

The subquery with the two WHERE clauses must be processed first, so that it
can be outer joined. When I was trying to limit the result set before, with
the WHERE clauses at the end, it was eliminating the open results I wanted from
the nested outer joins to zero or one, depending on how I phrased it. The
lesson for me is that while ANSI-92 is certainly more capable and precise, it
is also very particular about syntax and the order of phrasing in the query,
which I've never had an issue with in T-SQL.

[paross1]

Excellent! Glad that you were able to finally get the results that you were
after. I know, after writing queries in Oracle for years, I am still adjusting
to the ANSI-92 SQL syntax and all of its implications. (Just be thankful that
you aren't using an Access database. It is real particular about needing to
place parentheses in the joins, at least with Access 97.)

Phil