Re: count() and multiple tables

On Mon, 19 Mar 2001, Joseph Shraibman wrote:

> I want to select all the entries from d that have at least one
> corresponding entry in u that meets my conditions.  The problem is that
> count(*) is returning the number of corresponding entries in u, and I
> want only the number of entries in d.  How do I do this?
> 
> 
> create table d(
>                id int  primary key,
>                   status int default 1
> );
> 
> create table a(
>                key int primary key,
>                    status int default 1
> );
> 
> create table u(
>         dkey int not null,
>     akey int not null,
>     b bool DEFAULT false,
>         status int default 1,
>     primary key (dkey, akey) 
> );
> 
> insert into d values (1, 2);
> 
> insert into a values (1, 3);
> insert into a values (2, 3);
> insert into a values (3, 3);
> 
> insert into u values(1,1,false,2);
> insert into u values(1,2,false,1);
> insert into u values(1,3,false,2);
> 
> select count(*) from d where status = 2 and d.id = u.dkey and u.status =
> 2 and not u.b and u.akey = a.key and a.status = 3;

And postgres tries to be helpful again... :(  [I *really* dislike this
adding to from list thing]  Technically the above should be illegal
because no from list contains u or a.  Postgres is adding them to the
from list for you.

I think you want something like (untested):
select count(*) from d where status=2 and
exists (select * from u, a where u.dkey=d.id and u.status=2 and no u.b and u.akey=a.key and a.status=3
);