Reputation: 7793
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
Upvotes: 3
Views: 1702
Reputation: 301135
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;
Upvotes: 6
Reputation: 14212
Would that not be a left outer join you are looking for? I may have my terminology mixed up? Would not be the first time. But Aron's answer would work.
Upvotes: 0
Reputation: 34739
You're using count without group by for a start, and you're mixing "where" and "on" syntax for joins. Try this:
SELECT p.first_name, p.last_name, COUNT(i.student)
FROM person p
JOIN student s on p.id = s.person
LEFT JOIN incident i ON s.id = i.student
WHERE s.year_group LIKE "%Year 9%"
GROUP BY P.id;
Upvotes: 0
Reputation: 72039
Alternately, you could avoid the LEFT JOIN
by using a correlated subquery:
SELECT
p.first_name
, p.last_name
, (SELECT COUNT(*) FROM incident i WHERE i.student = s.id)
FROM
person p JOIN student s on s.person_id = p.id
WHERE
s.year_group LIKE "%Year 9%"
Upvotes: 0