From: | Joseph Shraibman <jks(at)selectacast(dot)net> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | count() and multiple tables |
Date: | 2001-03-19 23:37:30 |
Message-ID: | 3AB6983A.E64A6AD6@selectacast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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;
/* that returns 2 when I want it to return 1 */
drop table d;
drop table a;
drop table u;
--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-03-19 23:49:02 | Re: Manual Trigger Creation |
Previous Message | Josh Berkus | 2001-03-19 23:34:29 | Re: Manual Trigger Creation |