I have big problems understanding if and how MySQL (4.0) handles nesting of LEFT OUTER, RIGHT OUTER and INNER JOINS when more than two joins are used.

I have a simple a<-b->c relationship with a(aid), b(aid,cid) c(cid)

Eg 1:

SELECT a.aid, b.aid, b.cid, c.cid
FROM b INNER JOIN c ON b.cid = c.cid
RIGHT JOIN a ON a.aid = b.aid

In this case I would expect b to be joined with c, and the result of that to be right joined with a. But that does not happen, the join becomes cartesian.

Eg 2:

SELECT a.aid, b.aid, b.cid, c.cid
FROM a LEFT JOIN (b INNER JOIN c ON b.cid = c.cid)
ON a.aid = b.aid

I would expect the same result from this statement, first an inner join between b and c, and then left joined with a. But again I get a cartesian join.

I guess the real question is in which order MySQL executes the JOIN statements if there are several, and if there is a way to control it. Obviously brackets and/or the order of the ON conditions does not work.

Is this unsupported in MySQL 4.0?

Any feedback would be most appreciated.

any feedback? sure

the design of B leads me to believe that B.aid and B.cid are foreign keys

now, we all know mysql does not support foreign keys, so you have to do it yourself in your own application code

that said, you should never find a row of B where B.aid exists but B.cid is null or refers to a C.cid that doesn't exist

therefore if you do A left outer B, you can carry right on and do B left outer C, because the B left outer C part of it will always give the same results as B inner C

select A.aid, B.aid, B.cid, C.cid
from A
left outer
join B
on A.aid = B.aid
left outer
join C on B.cid = C.cid

(i learned this technique on early versions of db2 where you could not have A left outer B inner C, even if you parenthesized them)

this left outer technique should obviously also work with right outers, but i never code right outer, call it a personal peccadillo, i just always rewrite them as left outers

rudy
http://r937.com/

I don't understand 'we all know mysql does not support foreign keys'.
Innodb tables do.

Try 'SELECT STRAIGHT_JOIN ...' to enforce joins executed
in left to right order as shown in FROM clause.

vaneki, you're right, especially now that mysql includes sap

for most people, mysql means myisam, but i guess i should have been more explicit

Thanks, but i finally found the problem. This is bug/lack-of-feature in MySQL, that does not allow nested JOIN statements. (http://bugs.mysql.com/bug.php?id=1591 and bug 1674).

Rudy, you're right about the foreign key, however reality is a bit more complex than my example. But maybe you have a smart work around for this bug in MySQL (in Orcle it would have been trivial with exists/not exists and subselect, and i DB2 I assume eg 2 would have worked).

What I'm trying to do is the following, assuming table a is a list of students, c is a list of courses and b is the releation between the two. I want to query all students, and if they have taken a specific course return that name:

SELECT a.aid, a.name, IFNULL(c.name,'-')
FROM a
LEFT JOIN b ON a.aid = b.aid
LEFT JOIN c ON b.cid = c.cid
WHERE (c.cid IS NULL OR c.cid = X)

This will return all students not taking any courses at all (then c.cid will be null), and all students taking (at least) the X course. However (and the root of this problem in my application) all students taking other courses will not be fetched at all (since c.cid in that case will be something else than X). So I moved to the following instead:

SELECT a.aid, a.name, IFNULL(c.name,'-')
FROM a
LEFT JOIN (b
INNER JOIN c ON b.cid = c.cid AND c.cid = X)
ON a.aid = b.aid

Which works in an ANSI environment, but not in MySql

So the question is how I can get to the students taking other courses than X, and only return one row (so I can UNION this with my original SQL):

SELECT a.aid, a.name, IFNULL(c.name,'-')
FROM a
LEFT JOIN b ON a.aid = b.aid
LEFT JOIN c ON b.cid = c.cid
WHERE (c.cid IS NULL OR c.cid = X)
UNION
SELECT a.aid, a.name, '-'
FROM a
INNER JOIN b ON a.aid = b.aid
LEFT JOIN c ON b.cid = c.cid AND c.cid = X
WHERE c.cid IS NULL

This is very close, where the second SQL returns all students taking at least one course not being X. However, the problem in this case is that I would get multiple rows if a student take two or more courses not being X (could I use HAVING COUNT?), but more difficult is to avoid a student that takes both X and another course, he would be fetched in both statements.

So the question is how do I fetch ONE row for each student that takes at least one course, and does not take X???

Any idea?
Thx

Fetch ONE row for each student that takes at least one course:

SELECT a.aid, a.name, sum(if(c.cid=X,1,0)) as count
FROM a, b, c
WHERE a.aid = b.aid
AND b.cid = c.cid
GROUP BY a.aid, a.name
HAVING count < 1;


Fetch ONE row for each student that takes 0 or more courses:

SELECT a.aid, a.name, sum(if(c.cid=X,1,0)) as count
FROM a LEFT JOIN b ON a.aid = b.aid
LEFT JOIN c ON b.cid = c.cid
GROUP BY a.aid, a.name
HAVING count < 1;