I've been doing some experimenting with the data model from the "MySQL"
book (Addison Wesley). I have no trouble understanding joins between
two tables, but I'm finding it's a little confusing when 3 or more
tables are involved. I'm going to cite a particular set of tables and a
specific query. I would have assumed it would need to be one way, but
it actually requires a different approach, which I don't quite understand.
Here are the table creation scripts:
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (student_id)
) ENGINE = InnoDB;
CREATE TABLE grade_event
(
date DATE NOT NULL,
category ENUM('T','Q') NOT NULL,
event_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (event_id)
) ENGINE = InnoDB;
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
score_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (score_id),
INDEX (student_id),
FOREIGN KEY (event_id) REFERENCES grade_event (event_id),
FOREIGN KEY (student_id) REFERENCES student (student_id)
) ENGINE = InnoDB;
So, the query I want to build will list the quiz (not test) scores for a
particular student.
If I were to construct this "logically", I would think the query would
be this:
select score.score
from student left join score inner join grade_event
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
I visualize it as "student" joining to "score" joining to "grade_event".
Unfortunately, this query fails to parse with an unhelpful error message.
The query that works, with the joins out of the order I expected, is the
following:
select score.score
from student inner join grade_event left join score
on student.student_id = score.student_id and grade_event.event_id =
score.event_id
where student.student_id = 1 and grade_event.category='Q';
Can someone please go into detail of why what I first tried didn't work,
and why it needs to be the other way?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
