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

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 :-)

GreMa_at_t-online.de

          /|
         / |        
        /| |\
       /||  |\
      / O    |\         
     |        |\ Galopping Horse beats Running Man.
    /          |\
   /      /     |\
  /    __/|      |\
  \°   /  |       |\
   \/_/   |        |\