Re: challenging my wit: multiple outer joins between multiple tables.

From: DanHW <danhw_at_aol.com>
Date: 1 Jul 1998 02:59:30 GMT
Message-ID: <1998070102593000.WAA18582@ladder01.news.aol.com>

>
>Hi,
>
>you won't get all records with names in A, since der is no relationship
>between B.id and a field in A.
>
>Kal Khatib schrieb:
>>
>> I have the following sample tables
>>
>> TABLE A: with field: NAME
>>
>> TABLE B: with fields: ID, STATUS, UNITS_SOLD
>>
>> TABLE C: with fields: ID (same as B.ID), NAME (same as A.NAME)
>>
>> 1 - C.NAME is a subset of A.NAME
>> 2 - B.ID is a subset of C.ID
>> 3 - B.STATUS is X,Y, or Z. (if ID is in C but not in B, then there is no
>> status (transaction for this
>>
>> I'm trying to report the following
>>
>> A.NAME, STATUS, sum(UNITS_SOLD)
>>
>> For every name in A (even if no entries in C), report the status (if any
>> entries) along with the total UNITS_SOLD.
>>
>> I used
>>
>> select A.name, B.status, sum(unit_sold)
>> from A,B,C
>> where A.name = C.name (+)
>> and C.id = B.id (+)
>> group by A.name, B.status
>>
>> I don't get all possible values of A.Name. Only names that have entries
>> in C are reported.
>> I want to show all names in A, even if there are no entries in C for
>> that name.
>>
>> Any suggestions? Is there a better approach?
>> Help greatly appreciated.
>> Please copy me when responding to group.
>> thanks
>> Kal.
>
>--
>
>Regards
>
>Matthias Gresz :-)

select A.name, B.status, sum(unit_sold) from A,B,C
where A.name = C.name (+)

     and nvl(C.id,0) = B.id (+)
group by A.name, B.status

Think about what the outer join on C does... for a row from A with no match in C, a dummy row (with all columns NULL) is created. That row is then joined with the rows in B. Since a NULL is never = anything, the dummy rows created by the outer join never match anything. By using the nvl operator, the id in these dummy rows will be replaced by 0, which is then outer joined with the rows in B. This creates a dummy row in B, which you can then sum. If you want to provide a status for these, use a nvl on that column also...)

select A.name,

      nvl(B.status,'None'), 
      sum(unit_sold)

from A,B,C
where A.name = C.name (+)

     and nvl(C.id,0) = B.id (+)
group by A.name, B.status

Dan Hekimian-Williams Received on Tue Jun 30 1998 - 21:59:30 CDT