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 =]

Answers


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

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

Need Your Help

Rails Caching DB Queries and Best Practices

ruby-on-rails caching memcached

The DB load on my site is getting really high so it is time for me to cache common queries that are being called 1000s of times an hour where the results are not changing.

Set jQuery UI dialog button id?

javascript jquery jquery-ui

Is it possible to set the ID for the buttons in a jQuery UI dialog, so that I can refer to them later through jQuery? For example, trigger events, disable etc?