> 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.
Sorry, the cd_genre table would be the way to do it. This was a fairly
complex problem so I created the tables in a test database and wrote a
few queries that I think solve the problem for you, depending on how you
want select to return the genre list.
Here's a couple queries that will only get cd's that are not part of
Rock.
SELECT c.*
FROM cd AS c
WHERE 'Rock' NOT IN (SELECT g.genre FROM genre AS g, cd_genre AS cg
WHERE g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id);
SELECT c.*
FROM cd AS c
WHERE NOT EXISTS (SELECT NULL FROM genre AS g, cd_genre AS cg WHERE
g.genre_id = cg.genre_id AND cg.cd_id = c.cd_id AND g.genre = 'Rock');
The second one should be faster. The next query will show all cd's that
not exclusively Rock. (OK Computer should be in the result)
SELECT c.*
FROM cd AS c, cd_genre AS cg, genre AS g
WHERE c.cd_id = cg.cd_id
AND cg.genre_id = g.genre_id
AND g.genre != 'Rock';
If you add g.genre to any of the above queries you will get one row per
cd+genre combination. I dont know of any way to make that a delimited
list other than writing a function. So I wrote one for fun. The
argument is the cd_id.
CREATE OR REPLACE FUNCTION genre_list (integer) RETURNS TEXT AS '
DECLARE cdid ALIAS FOR $1; return_val TEXT; r RECORD;
BEGIN FOR r IN SELECT g.genre FROM genre AS g, cd_genre AS cg WHERE g.genre_id =
cg.genre_idAND cg.cd_id = cdid LOOP IF return_val IS NULL THEN return_val := r.genre; ELSE
return_val := return_val || '', '' || r.genre; END IF; END LOOP; RETURN return_val;
END
' LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;
Now I see that while composing this email some others have replied with
more simple solutions. For your case I prefer the added tables as they
enforce the possible list of genre's. Its also handy to keep them
seperate to get the list of genre's to display in a UI.
Eric
ps: aliasing all the table names is just my habit, do it however you see
fit. I also dont like to make my table names plural, its implied.
