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

Try something like:

 select A.name, B.status, sum(unit_sold)  from A,B,C
 where c.name = a.name (+)
   and b.id = c.id (+)
 group by A.name, B.status

Actually, I am not too sure that will work. My first inkling is

View of A&C  called d name,id
View of B&C  called e id,status,units
View of d&e  called F name,id,status,sum(units)
             group by name,id,status

In article <6n7nuq$5or$1_at_news00.btx.dtag.de>,   GreMa_at_t-online.de (Matthias Gresz) wrote:
>
> 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.
> / |\
> / / |\
> / __/| |\
> \° / | |\
> \/_/ | |\
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jun 29 1998 - 15:24:58 CDT