>>>>> "Fran" == Fran Fabrizio <ffabrizio@mmrd.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@stonehenge.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!
