Date: Wed, 30 Jul 2003 22:12:47 +0100
From: Richard Poole <richard@ruthie.org>
To: pgsql-sql@postgresql.org
Subject: Re: One to many query question
Message-ID: <20030730211247.GA8892@guests.deus.net>
References: <20030730193517.GB1383@neo.realtors.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030730193517.GB1383@neo.realtors.org>
User-Agent: Mutt/1.3.28i

On Wed, Jul 30, 2003 at 02:35:20PM -0500, 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)
> );

Assuming that each CD can have several rows in cd_genres,

SELECT id, artist, title
FROM cd
WHERE id NOT IN ( SELECT cd_id FROM cd_genres WHERE genre = 'Rock' );

will do what you want. Your co-worker is perhaps used to certain lesser
databases which don't support subselects...

Richard