challenging my wit: multiple outer joins between multiple tables.

From: Kal Khatib <kkhatib_at_cisco.com>
Date: Fri, 26 Jun 1998 11:49:01 -0700
Message-ID: <3593ED1C.EACA2E33@cisco.com>

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. Received on Fri Jun 26 1998 - 13:49:01 CDT