Message-ID: <008201c356e1$9245bca0$32021aac@chad>
From: "Chad Thompson" <chad@weblinkservices.com>
To: "Dave Dribin" <dave-ml@dribin.org>, <pgsql-sql@postgresql.org>
References: <20030730193517.GB1383@neo.realtors.org>
	<1059595896.12276.16.camel@eric>
	<20030730202736.GE1383@neo.realtors.org>
Subject: Re: One to many query question
Date: Wed, 30 Jul 2003 15:28:50 -0600
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

> 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

What you may be looking for is a not exists subselect.

Im not sure if this quite fits your example.. but maybe it will give you
some ideas...

SELECT
  cd.*,
  rock.genre AS rock,
  jazz.genre AS jazz,
  electronic.genre AS electronic
FROM cd
  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');
  WHERE NOT EXISTS
  (SELECT cd.id FROM cd join cd_genres rock ON (cd.id = rock.cd_id AND
rock.genre = 'Rock'))

This is quite fast in postgres unless configured wrong.. be sure to join
your subselect to your outer query.

Hope that helps
Chad