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!!