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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
