Subject: Re: One to many query question
From: Eric Clark <eclark@zerohp.com>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: <20030730202736.GE1383@neo.realtors.org>
References: <20030730193517.GB1383@neo.realtors.org>
	<1059595896.12276.16.camel@eric>
	<20030730202736.GE1383@neo.realtors.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 30 Jul 2003 14:42:06 -0700
Message-Id: <1059601326.12276.71.camel@eric>
Mime-Version: 1.0

> 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.