Re: One to many query question - Mailing list pgsql-sql

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