> Date: Sat, 16 May 2009 15:39:56 -0700
> From: davidmichaelk...@gmail.com
> To: mysql@lists.mysql.com
> Subject: Confused about syntax for specific join with 3 tables
>
> 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?
Hi David,
Well I could say it's probably because grade_event is a parent table while
score is a child table. And the parent joined first (you know, the deserved
respect) :)). Cheers.
Alugo Abdulazeez
www.frangeovic.com
_________________________________________________________________
Windows Live™: Keep your life in sync. Check it out!
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t1_allup_explore_012009
