X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id CE7AFD1C4D5 for ; Wed, 30 Jul 2003 22:33:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 93868-07 for ; Wed, 30 Jul 2003 19:32:05 -0300 (ADT) Received: from neo.dave.dribin.org (unknown [12.31.151.117]) by svr1.postgresql.org (Postfix) with ESMTP id E1075D1C4D0 for ; Wed, 30 Jul 2003 19:33:03 -0300 (ADT) Received: by neo.dave.dribin.org (Postfix, from userid 500) id 8DAD06034; Wed, 30 Jul 2003 18:33:06 -0400 (EDT) Date: Wed, 30 Jul 2003 17:33:06 -0500 From: Dave Dribin To: pgsql-sql@postgresql.org Subject: Re: One to many query question Message-ID: <20030730223306.GG1383@neo.realtors.org> Mail-Followup-To: pgsql-sql@postgresql.org References: <20030730193517.GB1383@neo.realtors.org> <3F2837FF.7020805@openratings.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3F2837FF.7020805@openratings.com> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/476 X-Sequence-Number: 14590 On Wed, Jul 30, 2003 at 05:26:23PM -0400, Dmitry Tkach wrote: > >How do I write a query to find all CDs that are NOT Rock? > > > What about > select * from cd where not exists (select 1 from cd_genres where cd_id > = cd.id and genre='Rock')? Thanks everyone! This did indeed work, and it does seem clearer. I never knew about EXISTS before. How portable is this? I'm interested in supporting PostgreSQL and MS SQL Server for now, and possibly Oracle and MySQL in the future. -Dave