Re: One to many query question

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 aboutselect * from cd where not exists (select 1 from cd_genres where cd_id 
= cd.id and genre='Rock')?

Dima