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?