0
assuming these 3 tables:
student - | student_id | name       | sex   | - 31 students
score   - | event_id   | student_id | grade | - 173 total grades
event   - | event_id   | type       | date  | - 6 events

this means there could be a total of 186 entries in the grade book,
6 for every student, some or none of which could be NULL.

I am trying to understand joins by working with these tables. I am trying to join them such that I can get all the grades all the students have in all of the various quizzes/tests assigned to them. Some students don't have grades for some events and therefore wouldn't show up in a standard inner joined query (as expected). However, I want all the students to show up, NULL or not, for every event there is that all students could have taken. However I can only successfully get back data for students who have a grade recorded in the scores table. I'm not exactly sure what I'm doing wrong or where to go from here.

The query I was trying:
SELECT st.student_id, st.name, e.date, e.type, sc.score
FROM event e LEFT JOIN score sc
ON e.event_id = sc.event_id
RIGHT JOIN student st
ON sc.student_id = st.student_id

Now I think I know WHY it's not working, just not the solution around it. LEFT and RIGHT joins work on the concept that if a record in the specified table doesn't match then the appropriate NULL values will be appended where needed. However, because all students have at least one grade, technically the RIGHT JOIN is satisfied since each student has at least one matching record. I can see how modifying my join between event and score can provably solve this easily, but again, I'm just not to sure what I'm doing.

Mike
  • 632
  • 7
  • 22
  • Start with students left join from student.student_id = score.student_id. Then left join from scores on score.event_id = event.event_id. – JonH Apr 10 '17 at 17:49
  • RIGHT JOIN is the same as LEFT JOIN with the tables re-ordered in the query really. – Mark Schultheiss Apr 10 '17 at 17:52

1 Answers1

2

First you need create the all 186 options event/student.

SELECT *
FROM student
CROSS JOIN event

Then you use LEFT JOIN to try to assign the score to each event

SELECT *
FROM student
CROSS JOIN event 
LEFT JOIN score
  ON score.student_id = student.student_id 
 AND score.event_id = event.event_id

Because you already have the 186 on the LEFT side, you will have those combinations with NULL value when doesn't find a match on the score table

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Ah, see this is what I was looking for, I wasn't sure how to build my initial data set to then join against whichever table was left over producing the data I want. CROSS JOIN's were never mentioned in any of the books I've been reading. – Mike Apr 10 '17 at 17:53
  • `CROSS JOIN` is equivalent to old notation `FROM student, event` but is preferred use that syntax to keep the same `INNER JOIN` format – Juan Carlos Oropeza Apr 10 '17 at 17:55
  • `cross join` from my own experience at least, is something that you don't stumble across all that often. I'm sorta surprised that it wouldn't be touched on in some manner with a SQL book though. – user2366842 Apr 10 '17 at 17:56
  • Oh my god. I didn't even realize the correlation. I got so caught up in the fact that I thought I didn't understand LEFT vs RIGHT that I totally didn't realize I could use the inner join syntax like that. In that case, CROSS joins are covered, I just didn't pay enough attention. Thanks everyone, this totally cleared up all confusion – Mike Apr 10 '17 at 17:59
  • everyone's gotta start somewhere. – user2366842 Apr 10 '17 at 18:01
  • If you want more examples, chech [**HERE**](https://www.w3schools.com/sql/sql_join.asp) – Juan Carlos Oropeza Apr 10 '17 at 18:02