On Wednesday 30 July 2003 20:35, 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? A co-worker > showed me the following query:
Basically you need to find all the CDs that ARE "rock" and subtract that set from the set of all CDs. You could use: SELECT id,artist,title FROM cd WHERE NOT EXIST (SELECT 1 FROM cd_genres WHERE cd_id=id AND genre='Rock'); or SELECT id,artist,title,cd_id FROM cd LEFT JOIN (SELECT cd_id FROM cd_genres WHERE genre='Rock') AS rock_cds ON cd_id=id WHERE cd_id IS NULL; or an EXCEPT clause etc. See which you like better. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match