OuterJoin Puzzle

From: Mr. Matteo diTommaso <ditommm_at_aa.wl.com>
Date: 17 Sep 93 15:51:16 -0400
Message-ID: <1993Sep17.155116.1_at_aa.wl.com>

Consider the following tables:     

BOOKCASE -< SHELVES -< BOOKS

BOOKCASE

---

CASEID
LOCATION
PURCHASE_DATE SHELVES

---

CASEID
SHELFNUM
SIZE BOOKS

---

CASEID
SHELFID
BOOKID
TITLE
AUTHOR For the sake of argument let's assume that you can have a book case with no shelves that does have books associated with it. Also there can be shelves with no books associated with them. The following is the query that I want to execute. But, I am not allowed to have more than one table outer join. How can I get the report that I want?

Query:
select x.LOCATION, y.SHELFNUM, z.TITLE, z.AUTHOR   from BOOKCASE x, SHELF y, BOOKS z

 where y.caseid  (+)= x.caseid
   and z.case_id (+)= x.caseid
   and z.shelfid (+)= y.shelf_id;

Report:

LOCATION  SHELFNUM  TITLE                AUTHOR
--------  --------  -------------------  ---------------
G1        1         Windows Programming  Petzold
G1        1         Guide to VMS         Ellis
G1        2         Algorithms in C      Sedgewick
G4        1
G4        2
G4        3
G5                  System Development   Fournier
G5                  Image Processing     D'Alleybrand
G5                  Distributed ORACLE   Webb
        

Is a UNION of two queries the only option?

select x.LOCATION, y.SHELFNUM, z.TITLE, z.AUTHOR   from BOOKCASE x, SHELF y, BOOKS z

 where y.caseid  (+)= x.caseid
   and z.case_id (+)= x.caseid
   and z.shelfid    = y.shelfid

UNION
select x.LOCATION, y.SHELFNUM, z.TITLE, z.AUTHOR   from BOOKCASE x, SHELF y, BOOKS z

 where y.caseid  (+)= x.caseid
   and z.case_id (+)= x.caseid
   and y.shelfid    = z.shelfid;

ADVthanksANCE

-- 


| Matteo diTommaso  (ditommm_at_aa.wl.com) |                                    |

| Ann Arbor, MI, USA                    |        This space for rent.        |

| Phone: (313) 996-7148                 |                                    |


| (Full disclaimer available by e-mail) |                                    |

Received on Fri Sep 17 1993 - 21:51:16 CEST