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:

Select *
From lessons
Left join students on
(lessons.lessonID = students.lessonID
AND students.studentID = #studentID#)
In my preferred formatting:

FROM lessons

ON (lessons.LessonID = students.LessonID AND students.studentID = #studentID#)

Michael Dawson adds that inequality operators can also be used:
Table1 INNER JOIN Table2 ON (Table1.pk = Table2.fk AND Table1.date <=
Thanks to Mark (and others at CF-Talk) for the help. I will certainly remember this one!

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
There is a small mistake in your code:
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.
# Posted By "O" | 1/15/07 2:15 AM
Thanks for the catch, O. I fixed it now.
# Posted By | 1/15/07 2:41 PM
I found this extremely helpful today and totally sped up my page loads thanks a bunch!!
# Posted By albert | 1/31/10 10:28 PM
Helped me. Thanks.
# Posted By Thihara | 5/21/12 2:05 AM
Your query will fail if either of either or both of these tables are joined to another table,
such that either or both lessonID and studentID are null or don't have matching rows.

Use instead selective matching which eliminates this possibility.
# Posted By DatabaseGuru | 10/28/14 11:05 AM
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.