-
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?
-
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
-
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"
-
You can use aliases (AS) only in ORDER BY and HAVING clauses.
-
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!!