From: Dave Dribin <dave-ml@dribin.org>
To: pgsql-sql@postgresql.org
Subject: One to many query question
Date: Wed, 30 Jul 2003 14:35:20 -0500
Message-ID: <20030730193517.GB1383@neo.realtors.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dave-ml@dribin.org
Subject: Re: One to many query question
In-Reply-To: <20030730193517.GB1383@neo.realtors.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox