How can I count all the records in the both tables in a 1:n relationship in 1 query?

Question

I'm guessing this a fairly easy question, but I've run into this problem a number of times and I can't find a solution either through searching (maybe I don't know what to search for) or trial and error.

I have the following table structure and data:

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL auto_increment,
  `table_b_id` int(11) NOT NULL,
  `field` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `table_b_id` (`table_b_id`)
);

INSERT INTO `table_a` VALUES(1, 1, 'test 1');
INSERT INTO `table_a` VALUES(2, 1, 'test 2');
INSERT INTO `table_a` VALUES(3, 0, 'test 3');

CREATE TABLE `table_b` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `table_b` VALUES(1, 'value 1');
INSERT INTO `table_b` VALUES(2, 'value 2');

As you can see, id 2 in table_b is not used in table_a and id 3 in table_a has a value of 0 for table_b_id. What I want to do is retrieve a count of the number of times each value in b is used in table_a, including 2 from table_b and count of all the values not in table_b.

I have come up with the following query:

SELECT b.name, COUNT(a.id) AS number
FROM table_a AS a
LEFT JOIN table_b AS b ON (b.id = a.table_b_id)
GROUP BY a.table_b_id

But it obviously only returns the following:

name       number
-----------------
null       1
value 1    2

How can I get the following, with only SQL:

name       number
-----------------
null       1
value 1    2
value 2    0

I am using MySQL. I'm guessing the answer is simple.

Edit: Is there no other way other than a union'd query?

Answer 1

SELECT b.name, SUM(case when a.id is null then 0 else 1 end) AS number
FROM table_a AS a
RIGHT JOIN table_b AS b ON (b.id = a.table_b_id)
GROUP BY b.id

If you want to get the null row, that is special. I believe you'll have to UNION it.

SELECT b.name, SUM(case when a.id is null then 0 else 1 end) AS number 
FROM table_a AS a
RIGHT JOIN table_b AS b ON (b.id = a.table_b_id)
GROUP BY b.id
UNION
select null, count(1) from table_a where table_b_id not in ( select id from table_b )

Answer 2

This nested query would work with mysql 5

SELECT b.name, (SELECT COUNT( * ) FROM table_a AS a WHERE a.table_b_id = b.id) AS amount FROM table_b AS b

Answer 3

What you want to do here is called FULL OUTER JOIN.

As for now, MySQL lacks this feature, that means you'll need to emulate is with a UNION (which is not too bad as you still do it in a single query).

SELECT  name, SUM(IF(a.id IS NULL, 0, 1))
FROM    table_b b
LEFT JOIN
        table_a a
ON      a.table_b_id = b.id
GROUP BY
        b.id
UNION ALL
SELECT  name, COUNT(*)
FROM    table_a a
LEFT JOIN
        table_b b
ON      b.id = a.table_b_id
WHERE   b.id IS NULL
GROUP BY
        b.id

Answer 4

There is a lengthy discussion of various ways to solve this problem on this post by Xaprb. He shows how to avoid using a UNION, although the alternative mechanism requires an extra "mutex" table and he suggests it's slower than using a UNION.