From: Dave Dribin <dave-ml@dribin.org>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: One to many query question
Date: Wed, 30 Jul 2003 15:27:36 -0500
Message-ID: <20030730202736.GE1383@neo.realtors.org> (raw)
In-Reply-To: <1059595896.12276.16.camel@eric>
References: <20030730193517.GB1383@neo.realtors.org>
<1059595896.12276.16.camel@eric>
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
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: <20030730202736.GE1383@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