Re: Query question, Need to write a query that returns only the students that have read all books by an author.

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 09 Jun 2007 01:07:28 +0200
Message-ID: <4669E130.4000701@gmail.com>

Albright.Brian_at_gmail.com schrieb:
> I need to write a query that returns only the students that have read
> all books by an author. I have these tables set up so far....
>
> create table Books (
> BookTitle varchar2(20) PRIMARY KEY,
> author varchar2(20)
> );
>
> create table BookCamp (
> MemberName varchar2(20),
> BookTitle varchar2(20),
> CONSTRAINT fk_BookTitle
> FOREIGN KEY (BookTitle)
> REFERENCES Books(BookTitle)
> );
>
> insert into Books values ('Psycho', 'Brian');
> insert into Books values ('Happy Rotter', 'Rocksteady');
> insert into Books values ('Goblet', 'J.K Rowling');
> insert into Books values ('Prisoner', 'J.K Rowling');
>
> insert into BookCamp values ('Bob', 'Psycho');
> insert into BookCamp values ('Chuck', 'Goblet');
> insert into BookCamp values ('Chuck', 'Prisoner');
> insert into BookCamp values ('Mike', 'Psycho');
> insert into BookCamp values ('Mike', 'Goblet');
> insert into BookCamp values ('Mike', 'Prisoner');
> insert into BookCamp values ('Mary', 'Goblet');
>
> So basically, if I inputted "J.K Rowling" the names "Chuck" and "Mike"
> should come up. If the author is "Brian" then the names "Bob" and
> "Mike" should come up. I've tried several things like...
> select membername from BookCamp where BookTitle in(select BookTitle
> from Books where (author = 'J.K Rowling')); but this obviously isn't
> quite there....Any Help?
>

SQL> SELECT DISTINCT membername

   2 FROM bookcamp bc
   3 WHERE NOT EXISTS (
   4 SELECT NULL
   5 FROM bookcamp bc1
   6 PARTITION BY (membername)
   7 RIGHT OUTER JOIN books b
   8 ON (bc1.booktitle=b.booktitle)
   9 WHERE b.author='J.K Rowling'
  10 AND bc.membername=bc1.membername AND bc1.booktitle IS NULL)   11 /

MEMBERNAME

---

Chuck
Mike

?

Best regards

Maxim Received on Fri Jun 08 2007 - 18:07:28 CDT