Re: Slightly OT: outer joins

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