From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | will trillich <will(at)serensoft(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: not grokking outer joins... |
Date: | 2001-08-21 05:36:33 |
Message-ID: | Pine.BSF.4.21.0108202232480.3144-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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"
From | Date | Subject | |
---|---|---|---|
Next Message | Andrey Y. Mosienko | 2001-08-21 05:56:59 | Re: Sort by foreign date column |
Previous Message | Stephan Szabo | 2001-08-21 05:32:39 | Re: Sort by foreign date column |