Re: 3 Table Outer Join

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: 2000/04/12
Message-ID: <8d2ieb$89n$1@nnrp1.deja.com>#1/1

I'm a little confused about what you are attempting to do here, so if my response is off base, please provide more clarification.

First, why are you performing an outer join with AND a NOT IN on the rental table? Removing the 'rental' table from the FROM clause and removing the outer join will result in the same output. Of course, you can no longer select 'rentid', but it would have been NULL in all cases anyway! You can see my confusion...

Second, use NOT EXISTS in place of NOT IN. NOT IN causes an internal sort and is thus slightly less efficient...

Hopefully, this is what you were looking for (I added a NULL column and alias so that it will return exactly the same as the old query):

SELECT

  t.tape
, b.book
, b.title

, NULL rentid
FROM
  tapes t
, books b
WHERE b.bookid=t.bookid
AND NOT EXISTS(

	SELECT
	  'x'
	FROM
	  rentals r
	WHERE r.tapeid = t.tapeid
	);

That takes care of making your query more efficient, but I'm trying to understand how it is 'misbehaving' in your mind. I think what you are saying is that it is returning you the same book titles multiple times. This is because there can be multiple tapes per book (according to your design). If you want just a list of books that are not currently rented, use DISTINCT (you should take out the 't.tape' column as well). If you are looking for a list of books that aren't rented and the number of tapes associated with it, you can do a COUNT and GROUP BY. Run the above query. Give us some sample output, as well as what you want it to return, and we'll be able to provide a better answer.

HTH,
Michael J. Ort

In article <1ac2abca.431acb4e_at_usw-ex0105-038.remarq.com>,   lh1010 <lhendersNOlhSPAM_at_polaris.umuc.edu.invalid> wrote:
> I'm trying to perform an outer join using 3 tables. The tables
> track books on tape & are as follows:
>
> BOOKS- has book id & book info
> TAPES- has tape id, bookid table & other info
> RENTALS- rental id, tape id & rental info
>
> (note: if 1 tape has been rented, the book is considered rented)
>
> Here is the query I've been working with. The results give me
> all tapeid's & book titles that have not been rented...when I
> only want the book title(s) with absolutely no tape rentals.
> Thanks in advance for your help. lh
>
> select t.tape, b.book, b.title, r.rentid
> from tapes t, books b, rentals r
> where b.bookid=t.bookid
> and t.tapeid not in(select tapeid from rentals)
> and t.tapeid=r.tapeid(+);
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
 Network *
> The fastest and easiest way to search and participate in Usenet -
 Free!
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 12 2000 - 00:00:00 CDT