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