Results 1 to 5 of 5

Thread: Tricky 3-table join

  1. #1
    Join Date
    Jan 2003
    Location
    Surrey, UK
    Posts
    3

    Question Tricky 3-table join

    I have 3 tables, A, B and C. They are related as follws: A->C is one-to-many and B->C is one-to-many. The primary key of C is a combination of the foreign keys to A and B. This means that for any combination of A and B there may be 0 or 1 occurrences on C.

    I am trying to generate a query that for a particular value of A it will list all occurrences of B and show me whether an occurrence exists or not on C for that combination of A and B.

    Example:

    Table A contains A1, A2 and A3.
    Table B contains B1, B2, B3, B4, B5 & B6.
    Table C contains A1B1, A1B3, A1B5

    I want the query to show:

    A1, B1, true
    A1, B2, false
    A1, B3, true
    A1, B4, false
    A1, B5, true
    A1, B6, false

    Is this possible with a single query? Or do I have to do it in two passes?

  2. #2
    Join Date
    Jan 2003
    Location
    Budapest, Hungary
    Posts
    2
    Hello Tony,

    SELECT a.m, b.m, IF(CONCAT(a.m, b.m)=c.m, 'true', 'false') AS x
    FROM a, b
    LEFT JOIN c ON (a.m=LEFT(c.m,2) AND b.m=RIGHT(c.m,2));

    | a1 | b1 | true
    | a1 | b2 | false
    | a1 | b3 | true
    | a1 | b4 | false
    | a1 | b5 | true
    | a1 | b6 | false

  3. #3
    Join Date
    Jan 2003
    Location
    Surrey, UK
    Posts
    3
    Thanks for that reply. I think I confused you by saying that the primary key of C was a combination of the foreign keys to A and B. What I meant was that C's primary key was comprised of two fields, those two fields being the two foreign keys. There is no need to define a separate primary key as a field can be used as a primary and foreign key at the same time.

    I have adjusted your code to the following, and it works:

    SELECT a.a_id, b.b_id, IF(a.a_id=c.a_id AND b.b_id=c.b_id, 'true', 'false') AS x
    FROM a, b
    LEFT JOIN c ON (a.a_id=c.a_id AND b.b_id=c.b_id)
    WHERE a.a_id='a1'

    My next question is: is it possible to include the value of 'x' (which is either 'true' or 'false' in this example) in the WHERE clause? I have tried it, but the error message says "Unknown column 'x' in 'where clause"

  4. #4
    Join Date
    Jan 2003
    Location
    Budapest, Hungary
    Posts
    2
    You can use aliases (AS) only in ORDER BY and HAVING clauses.

  5. #5
    Join Date
    Jan 2003
    Location
    Surrey, UK
    Posts
    3
    After a bit of experimenting I have come up with what I think to be the most flexible solution:

    SELECT a.a_id, b.b_id, c.a_id AS x
    FROM a, b
    LEFT JOIN c ON (a.a_id=c.a_id AND b.b_id=c.b_id)
    WHERE a.a_id='a1'

    In this case the 'x' column is either NULL or NOT NULL (the actual value is irrelevant) depending on whether an entry exists on table C for a particular combination of A and B.

    I can filter this even further by appending either
    AND c.a_id IS NOT NULL
    or
    AND c.a_id IS NULL
    to the WHERE clause

    Thanks for your help!!

Posting Permissions

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