Mysql – Why is this LEFT JOIN eliminating records with nothing in the other table

join;left-joinMySQLsql

I have a MySQL Left Join problem.

I have three tables which I'm trying to join.

A person table:

CREATE TABLE person (
    id INT NOT NULL AUTO_INCREMENT,
    type ENUM('student', 'staff', 'guardian') NOT NULL,
    first_name CHAR(30) NOT NULL,
    last_name CHAR(30) NOT NULL,
    gender ENUM('m', 'f') NOT NULL,
    dob VARCHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

A student table:

CREATE TABLE student (
    id INT NOT NULL AUTO_INCREMENT,
    person_id INT NOT NULL,
    primary_guardian INT NOT NULL,
    secondary_guardian INT,
    join_date VARCHAR(30) NOT NULL,  
    status ENUM('current', 'graduated', 'expelled', 'other') NOT NULL,
    tutor_group VARCHAR(30) NOT NULL,
    year_group VARCHAR(30) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (person_id) REFERENCES person(id) ON DELETE CASCADE,
    FOREIGN KEY (primary_guardian) REFERENCES guardian(id),
    FOREIGN KEY (secondary_guardian) REFERENCES guardian(id),
    FOREIGN KEY (tutor_group) REFERENCES tutor_group(name),
    FOREIGN KEY (year_group) REFERENCES year_group(name)
);

And an incident table:

CREATE TABLE incident (
    id INT NOT NULL AUTO_INCREMENT,
    student INT NOT NULL,
    staff INT NOT NULL,
    guardian INT NOT NULL,
    sent_home BOOLEAN NOT NULL,
    illness_type VARCHAR(255) NOT NULL,
    action_taken VARCHAR(255) NOT NULL,
    incident_date DATETIME NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (student) REFERENCES student(id),
    FOREIGN KEY (staff) REFERENCES staff(id),
    FOREIGN KEY (guardian) REFERENCES guardian(id)
);

What I'm trying to select is the first name, last name and the number of incidents for each student in year 9.

Here's my best attempt at the query: 

SELECT p.first_name, p.last_name, COUNT(i.student)
FROM person p, student s  LEFT JOIN incident i ON s.id = i.student 
WHERE p.id = s.person_id AND s.year_group LIKE "%Year 9%";

However, it ignores any students without an incident which is not what I want – they should be displayed but with a count of 0. If I remove the left join and the count then I get all the students as I would expect.

I've probably misunderstood left join but I thought it was supposed to do, essentially what I'm trying to do?

Thanks for your help,

Adam

Best Answer

What you are doing is fine, you just missed off the group by clause

SELECT p.first_name, p.last_name, COUNT(i.student)
FROM person p, student s  LEFT JOIN incident i ON s.id = i.student 
WHERE p.id = s.person_id AND s.year_group LIKE "%Year 9%"
GROUP BY p.first_name, p.last_name;

Here's some test data

insert into person values(1, 'student', 'Alice', 'Foo', 'f','1970-01-01');
insert into person values(2, 'student', 'Bob', 'Bar', 'm','1970-01-01');

insert into student values(1,1,0,0,'', 'current','','Year 9');
insert into student values(2,2,0,0,'', 'current','','Year 9');

insert into incident values(1,1,0,0,0,'flu','chicken soup', '2008-01-08');

And here's the output of the query with the group by added to it:

+------------+-----------+------------------+
| first_name | last_name | COUNT(i.student) |
+------------+-----------+------------------+
| Alice      | Foo       |                1 |
| Bob        | Bar       |                0 |
+------------+-----------+------------------+

You could further clean up the query by making join clauses from your where clause, and grouping on the person id:

SELECT p.first_name, p.last_name, COUNT(i.student)
FROM person p
INNER JOIN student s ON(p.id = s.person_id)
LEFT JOIN incident i ON(s.id = i.student)
WHERE s.year_group LIKE "%Year 9%"
GROUP BY p.id;
Related Topic