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

From: Rohrbacher, Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Sat, 27 Jun 1998 17:38:41 +0200
Message-ID: <35951201.64FB0523@sbox.tu-graz.ac.at>

HI Kal

> 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.

Its because of C.id = B.id(+) this will limit the query to only those of C no matter if you have C.name (+) or not

Try :

select A.name , X.status , X.units_sold from ( select name, status , sum( units_sold )

           from   B,C  where C.id = B.id                         -- outer

Join is not needed if it is right that you should get a row in C it there is no entry in B

          group by  name, status  )  X ,   A                    -- because

there wouldn't be any status to report where A.name = X.name(+)

Received on Sat Jun 27 1998 - 10:38:41 CDT