Why does this LEFT JOIN delete records with nothing in another table?

I have a problem with MySQL Left Join.

I have three tables that I am trying to join.

Face 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)
);

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 the 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)
);

, , - , 9- .

:

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%";

, , - , 0. , , .

, , , , , ?

,

+3
4

, , ,

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;

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');

, :

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

, where :

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;
+5

, ? , ? . .

0

LEFT JOIN :

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%"
0

, "where" "on" . :

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;
0

All Articles