Received: from p1.selectacast.net (p1.selectacast.net [206.41.1.251]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2JNbXN78460 for ; Mon, 19 Mar 2001 18:37:34 -0500 (EST) (envelope-from jks@selectacast.net) Received: from selectacast.net (IDENT:jks@d1 [209.219.91.66]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id SAA09723 for ; Mon, 19 Mar 2001 18:37:30 -0500 Message-ID: <3AB6983A.E64A6AD6@selectacast.net> Date: Mon, 19 Mar 2001 18:37:30 -0500 From: Joseph Shraibman Organization: Xtenit, Inc. http://www.xtenit.com X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.4.2 i686) X-Accept-Language: en MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: count() and multiple tables Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/376 X-Sequence-Number: 2042 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@selectacast.net Increase signal to noise ratio. http://www.targabot.com