Find number of joins by a reference table

Apologies if the title is ambiguous, but I could not figure out a good way to title this problem.

In my database, I have a table J that joins tables A and B. That is to say, J has columns a_id, and b_id that hold the id of entries in A and B respectively. B also has a 'code' column in it; for the sake of example, let's say there are three entries with codes of 'CC', 'DD', and 'EE'.

Now, I want to write a query that lists how many A's have each type of code in B (how many A's have 'CC', how many have 'DD', and how many have 'EE').

I write that query as follows, and get the following output (fabricated data, of course -- sorted by alphabetical code order):

SELECT b.CODE as code, COUNT(*) AS COUNT FROM a, b, j
  WHERE j.a_id = a.id AND j.b_id = b.id
  GROUP BY b.CODE ORDER BY b.CODE

code  |  count
==============
CC    |  5
DD    |  10
EE    |  2

The problem occurs when I add in a new record to B, say with code 'FF'. Now at this point, I have no entries in J that point to code 'FF'开发者_运维知识库. So in the output, I want to include 'FF', but show that the count is 0 (no A's are joined to 'FF').

code  |  count
==============
CC    |  5
DD    |  10
EE    |  2
FF    |  0

However, with my current query, it doesn't do this and in fact leaves out all codes where the resulting count is 0.

Can any query masters out there help me to alter my query to include the counts for all codes, whether they are 0 or not? It would be greatly appreciated.

• Ian

You'll need to use a LEFT JOIN here to do this. The LEFT JOIN will give you all rows from table b, whether or not they have matching rows in table j.

SELECT b.CODE as code, COUNT(a.id) AS COUNT 
    FROM b
        LEFT JOIN j
            INNER JOIN a
                ON j.a_id = a.id
            ON j.b_id = b.id
    GROUP BY b.CODE 
    ORDER BY b.CODE

You need to use a LEFT JOIN:

SELECT b.CODE as code, COUNT(a.id) AS a_count
FROM b LEFT JOIN (j JOIN a ON j.a_id = a.id) ON j.b_id = b.id
GROUP BY b.CODE ASC