List:General Discussion« Previous MessageNext Message »
From:Jørn Dahl-Stamnes Date:September 11 2006 8:16am
Subject:Question about COUNT()
View as plain text  
Assume the following tables:

CREATE TABLE x_type (
  id	mediumint unsigned not null auto_increment,
  name	char(20),
  primary key (id)
)
INSERT INTO x_type (id,name) VALUES (1,'aa'),(2,'bb'),(3,'cc');

CREATE TABLE x_ref (
  id	mediumint unsigned not null auto_increment,
  type	mediumint unsigned not null,
  name  char(20),
  primary key (id)
)
insert into x_ref (type,name) values (1,'a-test'),(2,'b-test');

SELECT a.name,COUNT(b.id) FROM x_type AS a LEFT JOIN x_ref AS b ON 
(b.type=a.id) GROUP BY a.id;

This works fine. aa, bb and cc from x_types are shown:
+------+-------------+
| name | COUNT(b.id) |
+------+-------------+
| aa   |           1 |
| bb   |           1 |
| cc   |           0 |
+------+-------------+

Then add a column to x_ref:
alter table x_ref add column verified boolean default 0 after name;
update x_ref set verified=1;

Then I modify the query to:

SELECT a.name,COUNT(b.id) FROM x_type AS a LEFT JOIN x_ref AS b ON 
(b.type=a.id) WHERE b.verified=1;

If I do the query now, only 'aa' and 'bb' from the x_type table is listed. Not 
the 'cc' with count 0:
+------+-------------+
| name | COUNT(b.id) |
+------+-------------+
| aa   |           1 |
| bb   |           1 |
+------+-------------+

How can I get all records in x_type listet in one query?

-- 
Jørn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/
Thread
Question about COUNT()Jørn Dahl-Stamnes11 Sep
  • Re: Question about COUNT()Johan Höök11 Sep