Re: One to many query question

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