troubleshooting Question

Modify SQL to use OUTER JOIN

Avatar of bjv211
bjv211 asked on
DatabasesMySQL ServerColdFusion Language
3 Comments1 Solution463 ViewsLast Modified:
Using CF to connect to Access 2007 DB, but this is more of a SQL question.

I have a table courseloads (facultyID, sectionID, clID) which keeps track of who's teaching what section. so if a professor is teaching a section their facultyID and the sectionID are put in the table

I want to run a query to produce all the sections offered including those without assigned professors.  

I need to modify the attached SQL to show ALL sections including those not listed in the table 'courseloads'.  As of now it only shows records that appear in courseloads and sections.  I believe I need an outer join but don't know how to implement.
SELECT Courses.CourseTitle, Sections.SemCode, Courses.CourseID, Sections.SectionNumber, Sems.SemDetails, Faculty.LastName, Faculty.FirstName, Courses.ID, Sections.SectionID
FROM (Sems INNER JOIN (Courses INNER JOIN Sections ON Courses.[ID] = Sections.[CourseID]) ON Sems.[SemCode] = Sections.[SemCode]) INNER JOIN (Faculty INNER JOIN CourseLoads ON Faculty.[schoolID] = CourseLoads.[FacultyID]) ON Sections.[SectionID] = CourseLoads.[SectionID]
ORDER BY courses.courseID, sems.semcode, sections.sectionnumber;

Open in new window

ASKER CERTIFIED SOLUTION
dqmq

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros