Re: Slightly OT: outer joins

>>>>> "Fran" == Fran Fabrizio <ffabrizio(at)mmrd(dot)com> writes:

Fran> This is a little off topic but this is the best source of SQL knowledge
Fran> I know about so hopefully this will be interesting enough for someone to
Fran> answer. :-)

Fran> I've got the following tables:

Fran> Table people
Fran> id fname lname
Fran> 1 bob smith
Fran> 2 tom jones
Fran> 3 jane doe
Fran> 4 mike porter

Fran> Table food
Fran> id favorite_food
Fran> 2 eggrolls
Fran> 3 ice cream

Fran> Table color
Fran> id color
Fran> 1 red
Fran> 3 blue

Fran> I want a query to produce the result set:

Fran> fname lname favorite_color favorite_food
Fran> bob smith red null
Fran> tom jones null eggrolls
Fran> jane doe blue ice cream
Fran> mike porter null null

Fran> I'm having lots of trouble getting the right result or knowing whether
Fran> this is even a valid usage of outer joins. Can somebody show me a
Fran> working query? Thanks!

Got it on the first try:

test=# select * from people natural left join color natural left join food;
id | fname | lname | color | favorite_food
----+-------+--------+-------+---------------
1 | bob | smith | red |
2 | tom | jones | | eggrolls
3 | jane | doe | blue | ice cream
4 | mike | porter | |
(4 rows)

That's presuming "id" matches "id" in each table, thus the easy
use of the "natural" keyword. The "left join" is what gives you
nulls on the right.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!