From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Joseph Shraibman <jks(at)selectacast(dot)net> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: count() and multiple tables |
Date: | 2001-03-19 23:52:12 |
Message-ID: | Pine.BSF.4.21.0103191549230.26890-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
);
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2001-03-19 23:52:27 | Re: count() and multiple tables |
Previous Message | Stephan Szabo | 2001-03-19 23:49:03 | Re: Manual Trigger Creation |