LEFT JOIN to INNER JOIN multiple tables

Question

After a day of digging around trying to get this query to work, I've had to resort to asking for help. This is my first venture into JOINs so please treat me gently ;)

I've got a query producing a timetable based on data across 6 tables. Database relationship diagram

My query is:

SELECT
course.CourseName,
course.CourseID,
timetablepaeriods.PeriodName,
subject.SubjectName,
Subject.SubjectColour,
Room.RoomName
FROM
TimetablePeriods
LEFT JOIN Timetable ON
TimetablePeriods.PeriodID = Timetable.Period_ID
INNER JOIN Course ON
Timetable.Course_ID = Course.CourseID
INNER JOIN Subject ON
Course.Subject_ID = Subject.SubjectID
INNER JOIN CourseMembership ON
CourseMembership.Course_ID = Course.CourseID
INNER JOIN Room ON
Timetable.Room_ID = Room.RoomID
WHERE CourseMembership.Student_ID = 123
ORDER BY TimetablePeriods.SortOrder ASC

This is returning all of the results that match but not the rows where there is a value in TimetablePeriods but nothing else.

CourseName | CourseID | PeriodName | SubjectName | etc . . .
-----------|----------|------------|-------------|
y7Ma3      |  19      | MonP1      | Maths       |
y7Hist4    |  16      | MonP2      | History     |
y7Geog1    |  30      | MonP3      | Geography   |
y7Eng3     |  28      | MonP5      | English     |

I was expecting to get a row with blank values for MonP4. This exists in the database and if I run the same query against a student who has a blank against MonP5 it skips that instead.

As I said at the top this is my first attempt at using the JOIN statement if theres a better way of approaching this I'd love to hear it. Thanks in advance for any help.

you won't get rows from TimetablePeriods unless there are matching rows in all the tables where you've specified an INNER JOIN. That's what INNER JOIN does. If you want the rows from those other tables to be optional then use LEFT JOIN instead. Make sure you clearly understand the difference between the various types of JOIN. You can find explanations / examples in thousands of places online. — ADyson, Sep 13 '19 at 10:24
Thanks for your prompt responses I've swapped all of my INNER to being LEFT and I'm getting the same results as when I was using INNER. ... LEFT JOIN Timetable ON TimetablePeriods.PeriodID = Timetable.Period_ID LEFT JOIN Course ON Timetable.Course_ID = Course.CourseID LEFT JOIN Subject ON Course.Subject_ID = Subject.SubjectID LEFT JOIN CourseMembership ON CourseMembership.Course_ID = Course.CourseID LEFT JOIN Room ON Timetable.Room_ID = Room.RoomID — sibrows, Sep 13 '19 at 10:43
@sibrows whenever using `LEFT JOIN`, any conditions in the `WHERE` clause on right-side tables make them `INNER JOIN` effectively. Move your `WHERE` condition to `ON .. AND` clause. Check this answer for example: https://stackoverflow.com/a/57912435/2469308 — Madhur Bhaiya, Sep 13 '19 at 10:47
@AnkitBajpai did you read the previous comments before adding yours? That has already been suggested and indeed the OP has already done that (see comment immediately above yours). — ADyson, Sep 13 '19 at 10:59
@Madhur Bhaiya thanks for this. I attempted to move the WHERE to the ON .. AND that appeared to return results ignoring the Student_ID. I've now moved the bulk of the query to the inner SELECT statement as shown in your example, leaving me with a RIGHT JOIN on the end to get it working. — sibrows, Sep 13 '19 at 11:28
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. — philipxy, Sep 14 '19 at 02:22
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Also, links die. Insert images/links using edit functions. Make your post self-contained. — philipxy, Sep 14 '19 at 02:24

score 1 · Answer 1 · answered Sep 13 '19 at 11:39

1

As explained by @Madhur Bhaiya the WHERE statement in my original query was changing everything to an INNER JOIN

My solution

SELECT 
r.CourseName,
r.CourseID,
r.SubjectName,
r.SubjectColour,
r.RoomName,
TimetablePeriods.PeriodName
FROM
    (SELECT
    Course.CourseName,
    Course.CoureID,
    Subject.SubjectName,
    Subject.Colour,
    Room.RoomName,
    Timetable.Period_ID
    FROM
    Course,
    Timetable,
    Subject,
    CourseMembership,
    Room
    WHERE
    Course.CourseID = Timetable.Course_ID AND
    Course.Subject_ID = Subject.SubjectID AND
    Timetable.Room_ID = Room.Room_ID AND
    CourseMembership.Course_ID = Course.CourseID AND
    CourseMembership.Student_ID = 123) r
RIGHT JOIN TimetablePeriods ON
TimetablePeriods.PeriodID = r.Period_ID
ORDER BY TimetablePeriods.SortOrder ASC

answered Sep 13 '19 at 11:39

sibrows

11
1
2

I would point out that this syntax `FROM Course, Timetable, Subject, CourseMembership` is generally considered obsolete and it's clearer, safer and better practice to use the JOIN syntax instead of comma-separated values. – ADyson Sep 13 '19 at 11:52
No, the problem was not the where condition. It was an inner join condition. Learn what LEFT/RIGHT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left/right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right/left [sic] table column to be not NULL after a LEFT/RIGHT JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". – philipxy Sep 14 '19 at 02:31

LEFT JOIN to INNER JOIN multiple tables

1 Answers1