[SQL] One to many query question
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: SELECT cd.*, rock.genre AS rock, jazz.genre AS jazz, electronic.genre AS electronic FROM cd LEFT JOIN cd_genres rock ON (cd.id = rock.cd_id AND rock.genre = 'Rock') LEFT JOIN cd_genres jazz ON (cd.id = jazz.cd_id AND jazz.genre = 'Jazz') LEFT JOIN cd_genres electronic ON (cd.id = electronic.cd_id AND electronic.genre = 'Electronic'); This produces the following results, which seems to essentially de-normalize the data: id | artist | title| rock | jazz | electronic +---++--+--+ 1 | Miles Davis | Some Kind of Blue | | Jazz | 2 | Metallica | Ride the Lightning | Rock | | 3 | Chemical Brothers | Surrender | | | Electronic 4 | Radiohead | OK Computer| Rock | | Electronic (4 rows) Then to filter out those NOT Rock, I can add a: WHERE rock.genre IS NULL While, this *does* work, I have this feeling there is a better way (and I'm not sure of the performance). If I add more genres, I have to add more LEFT JOINs. I *could* actually create a column per genre, but this means adding and removing genres requires an alter table. And I'd rather actually normalize further such that the list of genres is in its *own* table. Any thoughts? I'm pretty much a SQL newbie, so pointers to good books or articles would also be helpful. Thanks! -Dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] One to many query question
On Wed, Jul 30, 2003 at 01:11:35PM -0700, Eric Clark wrote: > 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) > ); This doesn't allow multiple genre's per CD, though, does it? A CD can only have 1 genre_id. I would like the ability to have multiple genres, in which case a third table is necessary: CREATE TABLE cd_genres ( cd_id integer, genre_id integer ); cd_id references cd.id and genre_id references genre.genre_id. This still requires the complex LEFT JOIN query from my first post, too, I think, *plus* an extra join between cd_genres and genre. -Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] One to many query question
On Wed, Jul 30, 2003 at 05:26:23PM -0400, Dmitry Tkach wrote: > >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')? Thanks everyone! This did indeed work, and it does seem clearer. I never knew about EXISTS before. How portable is this? I'm interested in supporting PostgreSQL and MS SQL Server for now, and possibly Oracle and MySQL in the future. -Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
