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

Hi, 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

Answer 2

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

Edit

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