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?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Confused about syntax for specific join with 3 tables

--_00a68f7a-91c1-49dd-9871-b3ea9b3d9fe3_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable



=20

> Date: Sat=2C 16 May 2009 15:39:56 -0700
> From: davidmichaelkarr@gmail.com
> To: mysql@lists.mysql.com
> Subject: Confused about syntax for specific join with 3 tables
>=20
> I've been doing some experimenting with the data model from the "MySQL"=20
> book (Addison Wesley). I have no trouble understanding joins between=20
> two tables=2C but I'm finding it's a little confusing when 3 or more=20
> tables are involved. I'm going to cite a particular set of tables and a=20
> specific query. I would have assumed it would need to be one way=2C but=20
> it actually requires a different approach=2C which I don't quite understa=
nd.
>=20
> Here are the table creation scripts:
>=20
> CREATE TABLE student
> (
> name VARCHAR(20) NOT NULL=2C
> sex ENUM('F'=2C'M') NOT NULL=2C
> student_id INT UNSIGNED NOT NULL AUTO_INCREMENT=2C
> PRIMARY KEY (student_id)
> ) ENGINE =3D InnoDB=3B
>=20
> CREATE TABLE grade_event
> (
> date DATE NOT NULL=2C
> category ENUM('T'=2C'Q') NOT NULL=2C
> event_id INT UNSIGNED NOT NULL AUTO_INCREMENT=2C
> PRIMARY KEY (event_id)
> ) ENGINE =3D InnoDB=3B
>=20
> CREATE TABLE score
> (
> student_id INT UNSIGNED NOT NULL=2C
> event_id INT UNSIGNED NOT NULL=2C
> score INT NOT NULL=2C
> score_id INT UNSIGNED NOT NULL AUTO_INCREMENT=2C
> PRIMARY KEY (score_id)=2C
> INDEX (student_id)=2C
> FOREIGN KEY (event_id) REFERENCES grade_event (event_id)=2C
> FOREIGN KEY (student_id) REFERENCES student (student_id)
> ) ENGINE =3D InnoDB=3B
>=20
> So=2C the query I want to build will list the quiz (not test) scores for =
a=20
> particular student.
>=20
> If I were to construct this "logically"=2C I would think the query would=
=20
> be this:
>=20
> select score.score
> from student left join score inner join grade_event
> on student.student_id =3D score.student_id and grade_event.event_id =
> score.event_id
> where student.student_id =3D 1 and grade_event.category=3D'Q'=3B
>=20
> I visualize it as "student" joining to "score" joining to "grade_event".
>=20
> Unfortunately=2C this query fails to parse with an unhelpful error messag=
e.
>=20
> The query that works=2C with the joins out of the order I expected=2C is =
the=20
> following:
>=20
> select score.score
> from student inner join grade_event left join score
> on student.student_id =3D score.student_id and grade_event.event_id =
> score.event_id
> where student.student_id =3D 1 and grade_event.category=3D'Q'=3B
>=20
> 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=2C

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=2C the deserv=
ed respect) :)). Cheers.

=20

Alugo Abdulazeez

www.frangeovic.com


____________________________________________________________ _____
Windows Live=99: Keep your life in sync. Check it out!
http://windowslive.com/explore?ocid=3DTXT_TAGLM_WL_t1_allup_ explore_012009=

--_00a68f7a-91c1-49dd-9871-b3ea9b3d9fe3_--