Re: [SQL] One to many query question

[Dmitry Tkach]

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

Dima



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster