From: Eric Clark <eclark@zerohp.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: One to many query question
Date: 30 Jul 2003 14:42:06 -0700
Message-ID: <1059601326.12276.71.camel@eric> (raw)
In-Reply-To: <20030730202736.GE1383@neo.realtors.org>
References: <20030730193517.GB1383@neo.realtors.org>
<1059595896.12276.16.camel@eric>
<20030730202736.GE1383@neo.realtors.org>
> 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_id AND 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.
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: eclark@zerohp.com
Subject: Re: One to many query question
In-Reply-To: <1059601326.12276.71.camel@eric>
* 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