Hej Jörn,
the problem lies in that you added your
b.verified=1 condition in the WHERE part,
what happens then is that you more or less transform
your left join into an inner join as you add on a condtion
for existing rows. If you do your query as:
SELECT a.name,COUNT(b.id) FROM x_type AS a LEFT JOIN x_ref AS b ON
(b.type=a.id) AND b.verified=1
GROUP BY a.Id
it should work.
/Johan
Jørn Dahl-Stamnes skrev:
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?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
