How to return null on a left join field if value not exists

Question

I have five(5) tables: students, subjects, period_one, period_two and period_three. I'm performing a left join to select values from these five tables:

Students Table (students)

  student_id    | Name
  --------------|-------
  1             |John
  2             |Peter
  3             |Flomo 

Subjects Table (subjects)

  subject_id       |SubjectName
  -----------------|-------
  math101          |Mathematics
  eng201           |English
  lang303          |Language Arts

Period One Table (period_one)

  id|student_id |subject_id| score
  --------------|----------|-----
  1 |1          | math101  |99
  2 |2          | eng201   |88
  3 |3          | lang303  |77 

Period Two Table (period_two)

  id|student_id |subject_id| score
  --------------|----------|-----
  1 |1          | math101  |100
  2 |2          | eng201   |60
  3 |3          | lang303  |65 

Period Three Table (period_three)

  id|student_id |subject_id| score
  --------------|----------|-----
  1 |1          | math101  |71
  2 |2          | eng201   |51
  3 |3          | lang303  |71

Here is the query I'm using to retrieve records Query1:

SELECT period_one.student_id, period_one.subject_id, period_one.score, period_two.score,period_three.score

from period_one

LEFT JOIN period_two
ON period_one.subject_id = period_two.subject_id
AND period_one.student_id = period_two.student_id

LEFT JOIN period_three 
on period_one.subject_id = period_three.subject_id
AND period_one.student_id = period_three.student_id

WHERE period_one.student_id = 10      

The problem with the code above is if the student id I'm looking for is not in the very first table(periodOne) the left join is being apply to the query returns null even if records of that student are in the others tables(periodTwo and periodThree). I look the issue up(https://www.w3schools.com/sql/sql_join_left.asp) and verified that is was not the best way to do things.

I THEN MADE SOME CHANGES

So, I updated my query to look like this (Query2):

SELECT students.student_id, period_one.score, period_one.subject_id, 
period_two.score, period_two.subject_id, period_three.score, 
period_three.subject_id
from students

LEFT JOIN period_one
ON students.student_id = period_one.student_id

LEFT JOIN period_two
ON students.student_id = period_two.student_id

LEFT JOIN period_three 
ON students.student_id = period_three.student_id

WHERE students.student_id = 3 OR period_one.student_id = 3 OR period_two.student_id = 3 OR period_three.student_id = 3

This works perfectly and since the students table is the main table which all the period tables are referencing. With this if a student id is not within the period_one and period_two table but period_there, the studentId, subjectId, and score is return for that table.

THEN ANOTHER PROBLEM POPS OUT

Before I updated my code I was displaying records a bit the way I wanted it, but wasn't getting/retrieving records the desire way. That was what prompted me to change my query, because I noticed it was the issue.

Now, based on my first query(Query1) I was selecting the subject_id from the various tables in the select statement. When I'm displaying records I passed that subject_id return from the query into a function that gets the subject name for that id. This is how I was displaying my results:

This works if the student id is within the table assign to the from clause, otherwise it returns nothing. That was why I change my code.

But now that I have change my code to (Query2) I'm unable to display the subject id and its name because it is not within the students table. Here is a gist on how I'm displaying my records: https://gist.github.com/nathansiafa/e9d22791800d4ba3a00e2b98de52baec

Is there a way which I can make it work better? Will appreciate suggestions as well as feed backs. Thanks!

Answer 1

It seems that you are overthinking it a bit from a technical/programatical perspective and not focusing on the semantical meaning of the data you want to have.

What you really want is a list of subjects, and the scores for those subjects for a particular student. The student is somewhat orthogonal here, because are are building a table, but Student is not part of that table.

So step 1 would be to select the data we want - subjects and the scores for them:

SELECT s.subject_id, s.subject_name, p1.score period_1_score, p2.score period_2_score, p3.score period_3_score 
FROM subject s
LEFT JOIN period_one p1 ON p1.subject_id = s.subject_id 
    AND p1.student_id = 10
LEFT JOIN period_two p2 ON p2.subject_id = s.subject_id 
    AND p2.student_id = 10
LEFT JOIN period_three p3 ON p3.subject_id = s.subject_id 
    AND p3.student_id = 10
ORDER BY s.subject_name;

This will give you the table data you want - first of all the subjects, and then the scores where they exist.

Now, if you insist to load student data within the same query (I would advise to have a separate query SELECT * FROM student WHERE student_id=10 for just that), then you can left-join it on top:

SELECT s.subject_id, s.subject_name, p1.score period_1_score, p2.score period_2_score, p3.score period_3_score, st.name student_name
FROM subject s
LEFT JOIN period_one p1 ON p1.subject_id = s.subject_id 
    AND p1.student_id = 10
LEFT JOIN period_two p2 ON p2.subject_id = s.subject_id 
    AND p2.student_id = 10
LEFT JOIN period_three p3 ON p3.subject_id = s.subject_id 
    AND p3.student_id = 10
LEFT JOIN student st ON st.student_id = 10
ORDER BY s.subject_name;

Answer 2

To me the problem is the data isn't in normal form causing you the problem. Since subjects and scores are basically the same structure just without a period notation, I first union all three tables together and create a "Period" column in the result.

SELECT X.*, 1 as Period from Period_one X
UNION ALL 
SELECT Y.*, 2 as Period FROM PERIOD_TWO Y
UNION ALL
SELECT Z.*, 3 as Period FROM PERIOD_THREE Z

I then use as a inline view witch becomes part of the left join so we don't have the issue with missing students in certain periods in your first query.

SELECT Student_ID
     , max(Case when Period = 1 then B.Subject_ID end) as Period_one_Subject
     , max(case when Period = 1 then B.Score end) as Period_one_Score 
     , max(Case when Period = 2 then B.Subject_ID) end as Period_Two_Subject
     , max(case when Period = 2 then B.Score end as Period_two_Score 
     , max(Case when Period = 3 then B.Subject_ID end) as Period_Three_Subject
     , max(case when Period = 3 then B.Score end) as Period_Three_Score 
FROM STUDENTS S
LEFT JOIN (SELECT Y.*, 1 as Period from Period_one X
           UNION ALL 
           SELECT X.*, 2 as Period FROM PERIOD_TWO Y
           UNION ALL
           SELECT Z.*, 3 as Period FROM PERIOD_THREE Z) B
 on S.Student_ID = B.Student_ID
GROUP BY Student_ID

We use max and group by student to "Pivot" the data allowing us to show both score and subject.

if we need the actual subject name, it's a simple matter of left joining to your subject table based on the subject_ID key.

I am assuming that a student can only appear once in each of your period tables.