Re: OuterJoin Puzzle

> But, I am not allowed to have
> more than one table outer join.

That's right, and you are trying to outer join bookcase to both the tables shelves and books.

The primary key of bookcase is (CASEID).
The primary key of shelves  is (CASEID, SHELFID).
The primary key of books    is (CASEID, SHELFID, BOOKID).

On shelves: a foreign key (CASEID)          references the
                                            primary key of bookcase.
On books:   a foreign key (CASEID, SHELFID) references the
                                            primary key of shelves.

Because (I am assuming) you can't have a shelf without a bookcase (nor, indeed, a book without a shelf), whenever you fill a field SHELFID in the shelves table, then you also fill the corresponding field CASEID in the shelves table.

The same is also true for books (I am assuming): whenever you fill a field BOOKID in the books table, then you also fill the corresponding fields (CASEID, SHELFID) in the books table.

My point is that any entry in the books table refers to a specific shelf (identified by (CASEID, SHELFID)), so you should not attempt to split the reference over two tables, shelves And bookcase, like you do with the syntax:

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

Rather, you should say:

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 (+)= y.caseid        <<< the difference is in this line
   and z.shelfid (+)= y.shelf_id;

which works just fine.

Even though there is more than field from the books table being outer joined to the shelves table ("z.case_id (+)" And "z.shelfid (+)"), the important thing is that there is only one table (books) being outer joined to the shelves table, and only one table (shelves) being outer joined to the bookcase table.

Hank Robinson.
University of Warwick. U.K.
H.J.Robinson_at_warwick.ac.uk Received on Mon Sep 20 1993 - 21:37:22 CEST