Join on 3 tables

Question

i have currently 3 tables :

• Table A
• Table B
• Table C

There is a link between A & B and a link between B & C (A-B-C). The thing is that :

• It is possible to have a row in A but no not in B
• It is possible to have a row in B but not in A
• It is possible to have a row in B but not in C

In the end i would like to have a query which could give me the following row (where X represent the ID of the corresponding table) :

TableA|TableB|TableC
   X  |   X  |   X
   X  | null | null 
 null |   X  |   X
   X  |   X  | null 

I managed to have the case with TableA & TableB with the following query :

SELECT A.ID, B.ID
FROM TABLEA A
LEFT JOIN TABLEB B on (join condition)
UNION
SELECT A.ID,B.ID
FROM TABLE B
LEFT JOIN TABLEA A on (join condition)

Thank you for any help you may provide

Answer 1

If I understood it correctly then a FULL OUTER JOIN should do your work :

SELECT a.id,b.id,c.id
FROM   TableA a 
       FULL OUTER JOIN TableB b on a.id = b.id
       FULL OUTER JOIN TableC c on COALESCE(a.id,b.id) = c.id

SQL Fiddle

Answer 2

What you need is a FULL OUTER JOIN, however, you have tagged your post with sybase - it depends what you mean by that. Sybase ASE doesn't support FULL OUTER JOIN syntax, but SQL Anywhere does.