Message-ID: <3F2837FF.7020805@openratings.com>
Date: Wed, 30 Jul 2003 17:26:23 -0400
From: Dmitry Tkach <dmitry@openratings.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: Dave Dribin <dave-ml@dribin.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: One to many query question
References: <20030730193517.GB1383@neo.realtors.org>
In-Reply-To: <20030730193517.GB1383@neo.realtors.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dave Dribin wrote:

>Hi, I'm having trouble with what I think should be an easy query.  For
>simplicity, I will use a CD database as an example.  Each CD may have
>multiple genres.  Here's some sample data:
>
>Artist                  Title                           Genres
>----------------------  ------------------------------  ----------------
>Miles Davis		Some Kind of Blue		Jazz
>Metallica		Ride the Lightning		Rock
>Chemical Brothers	Surrender			Electronic
>Radiohead		OK Computer			Rock, Electronic
>
>For simplicities sake, let's ignore normalization on artist and genre,
>and say the tables look like:
>
>CREATE TABLE cd (
>	id integer unique,
>	artist varchar(25),
>	title varchar(25)
>);
>
>CREATE TABLE cd_genres (
>	cd_id integer,
>	genre varchar(25)
>);
>
>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')?

Dima