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.