Simple ANSI syntax Outer Join question

[Captain Special]

Hello. I have an application that uses many *= and =* outer joins in its
queries. In order to convert an older query into the proper syntax, I am
examining which ones we'll have to do this with.

Attached is the old code, followed by the first iteration (non-functional) of
SQL that is attempting to use LEFT OUTER JOIN syntax.... I have had a rough
time trying to find documentation for a query in which one table has multiple
outer joins.

What is the conceptual or syntactical error here? Thanks in advance!

SELECT tbl_user.*, tbl_user_level.user_level, tbl_user_scope.scope_type,
tbl_program.program, tbl_program.program_id, tbl_site.site, tbl_site.site_id

FROM tbl_user, tbl_user_level, tbl_user_scope, tbl_program, tbl_site
WHERE tbl_user.level_id = tbl_user_level.ID
AND tbl_user.scope_id = tbl_user_scope.ID
AND tbl_user.scope_program_id *= tbl_program.ID
AND tbl_user.scope_site_id *= tbl_site.ID



SELECT tbl_user.*, tbl_user_level.user_level, tbl_user_scope.scope_type,
tbl_program.program, tbl_program.program_id, tbl_site.site, tbl_site.site_id

FROM tbl_user, tbl_user_level, tbl_user_scope

LEFT OUTER JOIN tbl_program ON tbl_user.scope_program_id = tbl_program.ID
LEFT OUTER JOIN tbl_site ON tbl_user.scope_site_id = tbl_site.ID

WHERE tbl_user.level_id = tbl_user_level.ID
AND tbl_user.scope_id = tbl_user_scope.ID

[Captain Special]

Answer found immediately after post. For posterity, here it is: use ANSI the
whole way through:

SELECT tbl_user.*, tbl_user_level.user_level, tbl_user_scope.scope_type,
tbl_program.program, tbl_program.program_id, tbl_site.site, tbl_site.site_id

FROM tbl_user

LEFT OUTER JOIN tbl_program ON tbl_user.scope_program_id = tbl_program.ID
LEFT OUTER JOIN tbl_site ON tbl_user.scope_site_id = tbl_site.ID
JOIN tbl_user_level ON tbl_user.level_id = tbl_user_level.ID
JOIN tbl_user_scope ON tbl_user.scope_id = tbl_user_scope.ID

[paross1]

Couple of things. First, you might consider using table name aliases so that
you can use those to prefix your column names rather than having to repeat the
entire table name on each column. Also, since you are using ANSI-92 syntax, you
will need to use INNER JOINs on your other tables.

Phil




SELECT tu.*,
tul.user_level,
tus.scope_type,
tp.program,
tp.program_id,
ts.site, ts.site_id
FROM tbl_user tu INNER JOIN tbl_user_scope tus ON tu.scope_id = tus.ID
LEFT JOIN tbl_program tp ON tu.scope_program_id = tp.ID
LEFT JOIN tbl_site ts ON tu.scope_site_id = ts.ID

[Captain Special]

paross1,

Thanks for your suggestions. It was your suggestion (in another thread) that
I switch some old queries to ANSI-92 syntax in the first place, so I really
appreciate your helping me see that through. Along those lines, I am trying to
migrate 167 templates with *= style joins over to ANSI-92, based on your
suggestion.

I have already learned a lot about the new syntax, but there are some areas
I'm having a hard time finding documentation for. Do you have some recommended
SQL reading? I have actually been able to go very far with Forta's Teach
Yourself SQL in 10 Minutes, and O'Reilly's SQL in a Nutshell. And, in the
meantime, could you give me a sentence or two on why this doesn't work?



SELECT tbl_family.ID, tbl_family.assigned_id, tbl_family.contact_person,
COUNT(tbl_adult.ID) AS num_adults
FROM tbl_family
LEFT OUTER JOIN tbl_adult ON AND tbl_family.ID = tbl_adult.family_ID

WHERE tbl_family.site_id = <CFQUERYPARAM VALUE="#form.siteID#"
CFSQLTYPE="CF_SQL_INTEGER">
AND tbl_adult.is_enrolled = 'No'
GROUP BY tbl_family.assigned_id, tbl_family.ID, tbl_family.contact_person

[paross1]

Since I started out as an Oracle developer, and Oracle didn't implement this
syntax until version 9i, I was rather late in learning how to write queries
this way myself, as Oracle had its own proprietary way of doing outer joins,
etc. Having said that, my primary reference for MSSQL has been the good old
Books Online that you install with Enterprise Manager, or that you can download
from Microsoft.

Phil

[Captain Special]

Thank you!

[Captain Special]

I'd rather post this here than start another thread. i have a query that isn't
converting nicely

I will first post the original version, then my attempt at making it ANSI-92
conformant. The first works just as it should, but the second returns no
results if there are no records in tbl_part_adult for a particular adult_ID for
that fiscal year (as selected by two form variables that are part of the
previous template):



FIRST VERION: (works OK)

<CFQUERY NAME="get_partinfo" DATASOURCE="#request.dsn#" DBTYPE="ODBC">
SELECT tbl_part_adult.*,
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,
tbl_month.ID AS monthID, tbl_month.month_name, tbl_month.fy_order,
tbl_school_year.ID AS syID, tbl_school_year.school_year

FROM tbl_part_adult, tbl_adult, tbl_month, tbl_school_year
WHERE tbl_part_adult.adult_ID =* tbl_adult.ID
AND tbl_part_adult.month_ID =* tbl_month.ID
AND tbl_part_adult.year_id =* tbl_school_year.ID
AND tbl_adult.ID = <CFQUERYPARAM VALUE="#form.part_ID#"
CFSQLTYPE="CF_SQL_INTEGER">
AND tbl_school_year.ID = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">

ORDER BY tbl_month.fy_order
</CFQUERY>


SECOND QUERY: (no results if tbl_part_adult has no records for that adult's
participation info yet)

<CFQUERY NAME="get_partinfo" DATASOURCE="#request.dsn#" DBTYPE="ODBC">
SELECT tbl_part_adult.*,
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,
tbl_month.ID AS monthID, tbl_month.month_name, tbl_month.fy_order,
tbl_school_year.ID AS syID, tbl_school_year.school_year

FROM tbl_part_adult

LEFT OUTER JOIN tbl_adult ON tbl_adult.ID = tbl_part_adult.adult_ID
LEFT OUTER JOIN tbl_month ON tbl_month.ID = tbl_part_adult.month_ID
LEFT OUTER JOIN tbl_school_year ON tbl_school_year.ID =
tbl_part_adult.year_id

WHERE tbl_adult.ID = <CFQUERYPARAM VALUE="#form.part_ID#"
CFSQLTYPE="CF_SQL_INTEGER">
AND tbl_school_year.ID = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">

ORDER BY tbl_month.fy_order
</CFQUERY>

[paross1]

I have a hunch it is because you are essentially asking for your query to
select rows WHERE tbl_school_year.ID = ="#form.year_ID#" even though this table
is on the tail end of an OUTER JOIN to tbl_part_adult.

Phil



Try changing
AND tbl_school_year.ID = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">

to
AND tbl_part_adult.year_id = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">

[Captain Special]

Thanks for your continued help.

I follow your logic on the last try, but unfortunately the results are the
same: zilch if a participant record has not yet been created. And when I think
about it, if tbl_part_adult has no records to match the form variables, but
tbl_adult and tbl_school_year do, it seems that a WHERE clause would need to be
phrased the way I did it the first time. So, I guess this one's still up in
the air. Could it be possible that there is an issue fully implementing
ANSI-92 on Microsoft SQL Server 2000?

[paross1]

So, what you are saying is that tbl_school_year relates to tbl_part_adult, and
tbl_part_adult relates to tbl_adult, so how can you have a link between
tbl_adult and tbl_school_year without an entry in tbl_part_adult?

Anyway, does this give you anything more than you were geting?

Phil


SELECT tbl_part_adult.*,
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,
tbl_month.ID AS monthID, tbl_month.month_name, tbl_month.fy_order,
tbl_school_year.ID AS syID, tbl_school_year.school_year
FROM tbl_adult
LEFT JOIN tbl_part_adult ON tbl_adult.ID = tbl_part_adult.adult_ID
LEFT JOIN tbl_month ON tbl_month.ID = tbl_part_adult.month_ID
LEFT JOIN tbl_school_year ON tbl_school_year.ID = tbl_part_adult.year_id
AND tbl_school_year.ID = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">
WHERE tbl_adult.ID = <CFQUERYPARAM VALUE="#form.part_ID#"
CFSQLTYPE="CF_SQL_INTEGER">
ORDER BY tbl_month.fy_order

[paross1]

D'oh! Looking at your FIRST VERSION it just occurred to me that =* is actually
a RIGHT (OUTER) JOIN, not LEFT. You may need to tweak the WHERE clause, but is
this any better?

Phil



<CFQUERY NAME="get_partinfo" DATASOURCE="#request.dsn#" DBTYPE="ODBC">
SELECT tbl_part_adult.*,
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,
tbl_month.ID AS monthID, tbl_month.month_name, tbl_month.fy_order,
tbl_school_year.ID AS syID, tbl_school_year.school_year
FROM tbl_part_adult,
RIGHT JOIN tbl_adult ON tbl_part_adult.adult_ID = tbl_adult.ID
RIGHT JOIN tbl_month ON tbl_part_adult.month_ID = tbl_month.ID
RIGHT JOIN tbl_school_year ON tbl_part_adult.year_id = tbl_school_year.ID
WHERE tbl_adult.ID = <CFQUERYPARAM VALUE="#form.part_ID#"
CFSQLTYPE="CF_SQL_INTEGER">
AND tbl_school_year.ID = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">
ORDER BY tbl_month.fy_order
</CFQUERY>

[Captain Special]

Progress! (Not yet perfection, but who's complaining?) I did not know you
could put an AND in a JOIN. First, let me address the RIGHT OUTER JOIN thing
you noticed. You are right, that first iteration did have =* but in my
subsequent ANSI attempts I did change it to LEFT OUTER JOIN purposefully
(complete with switching the tbl_adult.ID = tbl_part_adult.adult_ID from one
side to the other) after my first attempt using RIGHT OUTER JOIN didn't work.
Though the documentation about outer joins I have says the two are logically
equivalent if the x = y relationship is just reversed to a y = x, I thought I'd
still give it a try.

Despite that, I went ahead and re-tried it, unfortunately to no avail.

However, there was a small improvement in trying to use your second-to-last
post. In putting the AND statement in the LEFT JOIN, there was one row of NULL
values returned. It did not pull the adult participant's info from the
tbl_adult, nor did it pull the month's name, but at least it was a change - all
previous attempts pulled zero recordcount.

Given some hope, I tried putting AND on the other condition that had been in
the where clause, like so:

RIGHT JOIN tbl_school_year ON tbl_part_adult.year_id = tbl_school_year.ID
AND tbl_school_year.ID = <CFQUERYPARAM VALUE="#form.year_ID#"
CFSQLTYPE="CF_SQL_INTEGER">

This pulled a gazillion records, some with legit data (for other participants)
and some null rows as well (though I suspect that those come attached to the
sets of data returned for participants who have some data recorded for some
months in a fiscal year, but not for all months in that fiscal year. So,
though you can see I am kind of fumbling around blindly, your suggestions are
at least giving me some additional things to try, and narrowing down the
possible causes for the ANSI query not working like the non-ANSI one.