How to return NULL values when joining multiple tables

Question

here are the contents of the tables.

mysql> desc student;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| name       | varchar(20)      | NO   |     | NULL    |                |
| sex        | enum('F','M')    | NO   |     | NULL    |                |
| student_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+------------+------------------+------+-----+---------+----------------+

mysql> desc grade_event;
+----------+------------------+------+-----+---------+----------------+
| Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| date     | date             | NO   |     | NULL    |                |
| category | enum('T','Q')    | NO   |     | NULL    |                |
| event_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
+----------+------------------+------+-----+---------+----------------+

mysql> desc score;
+------------+------------------+------+-----+---------+-------+
| Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
| student_id | int(10) unsigned | NO   | PRI | NULL    |       |
| event_id   | int(10) unsigned | NO   | PRI | NULL    |       |
| score      | int(11)          | NO   |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+

What I'm trying to accomplish is to display which students missed a quiz/test, found under 'category' in the grade_event table.

Here's what I've come up with, but am not generating any results;

select name, category, sc.event_id
from student s
join score sc on s.student_id=sc.student_id
join grade_event ge on sc.event_id=ge.event_id
where score is NULL
group by name, event_id;

I've also gone the route of attempting a subquery;

select name, category, sc.event_id
from student s
join score sc on s.student_id=sc.student_id
join grade_event ge on sc.event_id=ge.event_id
where score not in (select score from score)
group by name, event_id;

Any help would be appreciated.

BD01 · Accepted Answer · 2016-04-15 00:21:41Z

1

I think you should just replace your join by left join, join is an inner join in MySQL : http://dev.mysql.com/doc/refman/5.7/en/join.html

And take care with group by event_id, it can be useful to precise group by sc.event_id. I don't know in MySQL but in sql server it wouldn't work.

Your second query is necessarily empty as you ask a column to have its values not in its values :)

answered Apr 15, 2016 at 0:21

BD01

1167 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carlos Uribe Over a year ago

I made the suggested changes, but I'm still returning an empty set of results. With the subquery, that makes total sense. What would be a better way to query?

BD01 Over a year ago

Replacing the two joins by left join doesn't work ? Maybe it is différences between sql server and MySQL, it would work in sql as I understand how your tables are created. Anyway, @Shadow provided a working solution it seems

Shadow · Accepted Answer · 2016-04-15 00:27:51Z

0

You need to use an outer join instead of an inner join to get list of students that do not have a corresponding record in the events after creating a carthesian join of students and events:

select name, category, ge.event_id
from (student s
join grade_event ge) --no join condition creates a carthesian join
left join score sc on s.student_id=sc.student_id and sc.event_id=ge.event_id
where sc.score is NULL

answered Apr 15, 2016 at 0:27

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Collectives™ on Stack Overflow

How to return NULL values when joining multiple tables

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related