Обсуждение: not grokking outer joins...

i'm trying to wrap outer joins around my cerebellum:

    CUST:
        id serial
        name varchar(30)
    ITEM:
        cust integer references cust (id)
        data varchar(30)
    INFO:
        cust integer references cust (id)
        stuff text

for my query i'd like, for every customer, to have
item.* show up if it exists, and info.* show up
if it exists...

    select
        CUST.name,
        ITEM.data,
        INFO.stuff
    from
        CUST left join ITEM on (CUST.id=ITEM.cust),
        CUST left join INFO on (CUST.id=INFO.cust)
    .... not!

or does this need some subselect magic?

and (separate question) is there a way to be sure that ONE or the
OTHER or BOTH have joinable data, but don't show CUST if neither
ITEM nor INFO has a match?

    foreach CUST
        if ITEM matches
            show record
        elsif INFO matches
            show record
        else -- neither matches
            dont show squat, not even from cust
        end if
    end foreach

--
Khan said that revenge is a dish best served cold. I think
sometimes it's best served hot, chunky, and foaming.
    - P.J.Lee ('79-'80)

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

On Mon, 20 Aug 2001, will trillich wrote:

> i'm trying to wrap outer joins around my cerebellum:
>
>     CUST:
>         id serial
>         name varchar(30)
>     ITEM:
>         cust integer references cust (id)
>         data varchar(30)
>     INFO:
>         cust integer references cust (id)
>         stuff text
>
> for my query i'd like, for every customer, to have
> item.* show up if it exists, and info.* show up
> if it exists...
>
>     select
>         CUST.name,
>         ITEM.data,
>         INFO.stuff
>     from
>         CUST left join ITEM on (CUST.id=ITEM.cust),
>         CUST left join INFO on (CUST.id=INFO.cust)
>     .... not!
>
> or does this need some subselect magic?
>

I think
from
cust left join item on (cust.id=item.cust) left join
 info on (cust.id=info.cust)

might be what you want.

> and (separate question) is there a way to be sure that ONE or the
> OTHER or BOTH have joinable data, but don't show CUST if neither
> ITEM nor INFO has a match?
>
>     foreach CUST
>         if ITEM matches
>             show record
>         elsif INFO matches
>             show record
>         else -- neither matches
>             dont show squat, not even from cust
>         end if
>     end foreach

I'd say something like "where item.cust is not null or
info.cust is not null"