Subject: Re: One to many query question
From: Eric Clark <eclark@zerohp.com>
To: Dave Dribin <dave-ml@dribin.org>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <20030730193517.GB1383@neo.realtors.org>
References: <20030730193517.GB1383@neo.realtors.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 30 Jul 2003 13:11:35 -0700
Message-Id: <1059595896.12276.16.camel@eric>
Mime-Version: 1.0

On Wed, 2003-07-30 at 12:35, Dave Dribin wrote: 
> CREATE TABLE cd (
> 	id integer unique,
> 	artist varchar(25),
> 	title varchar(25)
> );
> 
> CREATE TABLE cd_genres (
> 	cd_id integer,
> 	genre varchar(25)
> );

I think you've got this backwards.  There is no advantage in the above
table's over simply having a genre varchar(25) in the cd table.

You really want:

CREATE TABLE genre (
	genre_id serial,
	genre varchar(25)
);

CREATE TABLE cd (
	cd_id integer unique,
	artist varchar(25),
	title varchar(25),
        genre_id varchar(25) references genre (genre_id)
);

> How do I write a query to find all CDs that are NOT Rock?  A co-worker
> showed me the following query:

Now the query is simple:

SELECT cd.*, genre.genre FROM cd, genre WHERE cd.genre_id =
genre.genre_id AND genre.genre != 'Rock';

Hope that helps,
Eric