Outer Join on Multiple Fields

Posted At : March 14, 2005 10:37 PM | Posted By : Steve
Related Categories: SQL

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:

[More]

Comments (5) | Print | del.icio.us | Digg It! | Linking Blogs
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment]
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
[Add Comment]
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.