Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unanswered: Nested outer/inner join problem

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  3. #3
    Join Date
    Nov 2003
    Posts
    91
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Posts
    2
    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

  6. #6
    Join Date
    Nov 2003
    Posts
    91
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •