JOIN three tables
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 =]
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
To understand why you don't get any results when TableC has no records, you need to learn a bit about JOIN types in SQL.
Basically, when you issue the query
TableA A JOIN TableB B ON A.id = B.a_id
you're telling the database to look at both TableA and TableB and fetch all pairs of rows which satisfy the join predicate (A.id = B.a_id). Therefore, if TableB is empty, the database can't find and pair of rows with the prementioned criteria.
This type of JOIN is called INNER JOIN and it is the most common type of join operation used.
In your case you want to fetch all rows from TableA X TableB and all relevant rows from TableC, if such rows exist (based on the join predictate "A.id = C.a_id". This is the case for an OUTER JOIN. The two most common types of such join are the LEFT JOIN (which includes all rows from the left table) and the RIGHT JOIN (which includes all rows from the right table).
In this case, your query should be:
SELECT A.* FROM (TableA A JOIN Table B B ON A.id = B.a_id) LEFT JOIN TableC C ON A.id = C.a_ID
I would suggest to have a look at the relevant Wikipedia page, if you want to know more about types of joins in SQL
By following the same logic, if you want to have all rows from TableA and only the relevant rows from tables TableB and TableC (if they exist), your query would become:
SELECT A.* FROM (TableA A LEFT JOIN Table B B ON A.id = B.a_id) LEFT JOIN TableC C ON A.id = C.a_ID