Re: Slightly OT: outer joins

>>>>> "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!