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. , , .
, , , , , ?
,