Re: 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(at)postgresql(dot)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