Show results of query even if there isn't a match

bjv211
bjv211 used Ask the Experts™
on
I have the query below which currently gets all sections where an Instructor has been assigned to a course section (via the CourseLoads table).  The problem is I want to get all of the sections including the ones where there is no assigned instructor (ie, including the ones where that are not yet in the CourseLoads table).

table structure:
Courses - ID, CourseID, CourseTitle,CourseDescription, CourseContactHrs
Sections - SectionID, SectionNumber, SectionTimes, SectionDays, CourseID
Faculty - univID, FirstName, LastName
CourseLoads - SectionID, FacultyID

Relationships - Courses.ID = Sections.CourseID
                         CourseLoads.SectionID = Sections.SectionID
                         CourseLoads.FacultyID = Faculty.univID
SELECT Faculty.LastName, Faculty.FirstName, Faculty.univID, Sections.SectionDays, Sections.SectionNumber, Sections.SectionTimes, Courses.CourseTitle, Courses.CourseDescription, Courses.CourseContactHrs, Courses.CourseID
FROM ((Courses INNER JOIN Sections ON Courses.[ID] = Sections.[CourseID]) INNER JOIN CourseLoads ON Sections.[SectionID] = CourseLoads.[SectionID]) INNER JOIN Faculty ON CourseLoads.[FacultyID] = Faculty.[univID]
WHERE Courses.[ID] = 1

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You need to use outer joins.
Something like this should work:
((Courses INNER JOIN Sections ON Courses.[ID] = Sections.[CourseID]) LEFT OUTER JOIN CourseLoads ON Sections.[SectionID] = CourseLoads.[SectionID]) LEFT OUTER JOIN Faculty ON CourseLoads.[FacultyID] = Faculty.[univID]
WHERE Courses.[ID] = 1
AneeshDatabase Consultant
Top Expert 2009
Commented:
SELECT Faculty.LastName, Faculty.FirstName, Faculty.univID, Sections.SectionDays, Sections.SectionNumber, Sections.SectionTimes, Courses.CourseTitle, Courses.CourseDescription, Courses.CourseContactHrs, Courses.CourseID
FROM ((Courses left JOIN Sections ON Courses.[ID] = Sections.[CourseID]) left JOIN CourseLoads ON Sections.[SectionID] = CourseLoads.[SectionID]) left JOIN Faculty ON CourseLoads.[FacultyID] = Faculty.[univID]
WHERE Courses.[ID] = 1

Author

Commented:
aneeshattingal, it seems arturrrro's post does what I need.  How is your post different?  or would it produce the same results?

thanks
LEFT JOIN and LEFT OUTER JOIN  are just the same thing - left outer join. 'OUTER' keyword is optional.

Author

Commented:
Excellent work, thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial