Re: 3 Table Outer Join

Outer join...we don't need no stinking outer join... ;)

SELECT
  b.book
, b.title
FROM
  books b
WHERE NOT EXISTS(
    SELECT
      'x'
    FROM
      rentals r
    , tapes t
    WHERE r.tapeid = t.tapeid
    AND b.bookid = t.tapeid
    );

HTH,
Michael J. Ort

In article <0b3e2568.69fe17ca_at_usw-ex0105-038.remarq.com>,   lh1010 <lhendersNOlhSPAM_at_polaris.umuc.edu.invalid> wrote:
> Unfortunately I must do this by an outer join. BTW, using the
> suggested query actually got me closer to what I want, but still
> not quite there so I'll try to clarify a bit more. I'm looking
> to get the book for which the tapes have never been rented
> (i.e., no one has ever rented 1 tape copy of a specific book).
> Right now, these are small tables so I did another query to
> check the answer & found out there is only 1 book that has never
> had any of its tapes rented. Unfortunately, what I end up with
> is all the tapes, and associated book title, that haven't been
> rented. For example, Clockwork Orange has 3 tapes associated
> with it & 2 have been rented....I don't want the book Clockwork
> Orange to be in my results because it has at least 1 tape
> rented. Below is a sample of the results using the suggested new
> query, but my end results should only include Star Wars.
> Thanks. lh
>
> TAPEID BOOKID TITLE RENTID
> ------ ------ ---------------------- ------
> 4400SW 010200 Star Wars
> 0002SW 010200 Star Wars
> 0003SW 010200 Star Wars
> 0004SW 010200 Star Wars
> 0022TM 099444 The Absent Man
> 0041CA 340000 California Hikes
> 0062BC 000044 Breakfast of Champions
>
> * 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 Thu Apr 13 2000 - 00:00:00 CDT