Re: Slightly OT: outer joins - Mailing list pgsql-general

It would probably be better to set it up like this, if that's what you're
going to do:

id    person_id    food_id    color_id
1    1                 2              3

etc...

person
1 bob smith
etc...

this way if there are two people named bob smith you still can have a unique
person.  Otherwise your table design breaks down at that point.

This is also probably the most normalized way to do it...

Note also that using this method, if you need to change a name, food, or
color, the changes will happen instantly across the whole table structure.

--Russell

----- Original Message -----
From: "Brian Avis" <brian.avis@searhc.org>
To: "Risko Peter" <rpetike@freemail.hu>
Cc: "Fran Fabrizio" <ffabrizio@mmrd.com>; <pgsql-general@postgresql.org>
Sent: Tuesday, November 20, 2001 9:13 AM
Subject: Re: [GENERAL] Slightly OT: outer joins


> I'm sort of a beginner myself so forgive me if this is wrong. But that
> solution sort of assumes that data is being put into the tables at the
> same time.
>
> Wouldn't a better solution be to setup the first table like this.
>
> people
>
> ------------------------------
> |  id  |  fname  |  lname  |  food_id  |  color_id  |
> ------------------------------
> |  1   |  bob       |  smith    |       2        |       3           |
> ------------------------------
>
>
> Then you should be able to do a normal join type select and get the
> right results no matter what.
>
>
> Risko Peter wrote:
>
> >On Tue, 20 Nov 2001, Fran Fabrizio wrote:
> >
> >>This is a little off topic but this is the best source of SQL knowledge
> >>I know about so hopefully this will be interesting enough for someone to
> >>answer. :-)
> >>I've got the following tables:
> >>Table people
> >>id    fname    lname
> >>1     bob       smith
> >>2     tom       jones
> >>3     jane      doe
> >>4     mike     porter
> >>Table food
> >>id    favorite_food
> >>2     eggrolls
> >>3     ice cream
> >>Table color
> >>id     color
> >>1      red
> >>3      blue
> >>I want a query to produce the result set:
> >>fname   lname   favorite_color  favorite_food
> >>bob      smith     red               null
> >>tom      jones     null              eggrolls
> >>jane      doe       blue            ice cream
> >>mike    porter     null             null
> >>I'm having lots of trouble getting the right result or knowing whether
> >>this is even a valid usage of outer joins.  Can somebody show me a
> >>working query?  Thanks!
> >>
> >Hi Fran!
> >
> >  I'm a beginner, and maybe I will misinform you, but I think in the
above
> >case you want your tables being joined by the ID column. In that case you
> >_should_ have a row in your auxiliary tables (color, food) for every
> >occuring IDs in the main table. It will solve your problem:
> >---
> >drop table people;
> >drop table food;
> >drop table color;
> >create table people(id int4,fname char(10),lname char(10));
> >create table food(id int4,favorite_food char(10));
> >create table color(id int4,color char(10));
> >copy food from stdin;
> >1
> >2 eggrolls
> >3 ice cream
> >4
> >\.
> >copy color from stdin;
> >1 red
> >2
> >3 blue
> >4
> >\.
> >copy people from stdin;
> >1 bob smith
> >2 tom jones
> >3 jane doe
> >4 mike porter
> >\.
> >select fname,lname,color,favorite_food from people,food,color where
> >    people.id=food.id and people.id=color.id;
> >
> >
> >Udv: rpetike
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> >subscribe-nomail command to majordomo@postgresql.org so that your
> >message can get through to the mailing list cleanly
> >
>
> --
> Brian Avis
> SEARHC Medical Clinic
> Juneau, AK 99801
> (907) 463-4049
> cd /pub
> more beer
>
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.298 / Virus Database: 161 - Release Date: 11/13/01


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com