sql - JOIN three tables

Question

I am trying to retrieve two sets of information (the red and blue portions of the diagram in the one query.

I thought I could do it using the sql as stated below but it does not return me anything when TableC has no records. If TableC has no records, I would still want this query to return me the results as indicated by the BLUE area.

SELECT A.* FROM TableA A

JOIN TableB B ON (A.id = B.a_id)

JOIN TableC C ON (A.id = C.a_id)

Appreciate any pointers to return me the red and/or blue segments. Thanks in advance =]

Answer 1

try something like this

SELECT A.* FROM TableA A
LEFT OUTER JOIN TableB B ON (A.id = B.a_id)
LEFT OUTER JOIN TableC C ON (A.id = C.a_id)
WHERE B.a_id IS NOT NULL 
  OR c.a_id IS NOT NULL