Zero Count records under left outer joins

[Brent B. Powers]

I've created and populated two simple tables:

create table a (id serial, d integer)
insert a (d) values (1)
insert a (d) values (2)
insert a (d) values (3)
insert a (d) values (4)

create table a (id serial, a integer)
insert b (a) values (1)
insert b (a) values (1)
insert b (a) values (4)
insert b (a) values (4)

Now, I'd like the id's of a that are not referred to in b... thus:

mysql>   select a.id
   ->     from a
   ->left join b on a.id = b.a
   -> group by b.a
   ->   having count(b.a) = 0;
+----+
| id |
+----+
|  2 |
+----+
1 rows in set (0.00 sec)

That's not right.... check all of the counts:
mysql>    select a.id, count(b.a)
   ->      from a
   -> left join b on a.id = b.a
   ->  group by b.a;
+----+------------+
| id | count(b.a) |
+----+------------+
|  2 |          0 |
|  1 |          2 |
|  4 |          2 |
+----+------------+
3 rows in set (0.00 sec)

Still not good.. where's 3?
mysql> select * from b;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    4 |
|  4 |    4 |
+----+------+
4 rows in set (0.00 sec)

Can anyone tell me why I don't see a.id = 3 having a reference count of 0?

Cheers

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]