Outer Join on Multiple Fields
I ran into a situation where I needed to do an outer join on more than one field (if only SQL supported such a thing). After banging my head against a wall for a bit, I asked the CF-Talk list.
Lo and behold! SQL does support exactly what I want. I was just to dim to try it myself. Mark Gaulin provided the answer:
In my preferred formatting:Select *
From lessons
Left join students on
(lessons.lessonID = students.lessonID
AND students.studentID = #studentID#)
SELECT StudentID
FROM lessons
LEFT OUTER JOIN students
ON (lessons.LessonID = students.LessonID AND students.studentID = #studentID#)
Michael Dawson adds that inequality operators can also be used:
Thanks to Mark (and others at CF-Talk) for the help. I will certainly remember this one!Table1 INNER JOIN Table2 ON (Table1.pk = Table2.fk AND Table1.date <=
Table2.billingDate)
Select *
From lessons
Left join lessons on
(lessons.lessonID = students.lessonID
AND students.studentID = #studentID#)
line 3 should read:
Left join students on
I found this through google. Thanks for the help.
such that either or both lessonID and studentID are null or don't have matching rows.
Use instead selective matching which eliminates this possibility.