Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Query question, Need to write a query that returns only the students that have read all books by an author.
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
?
Best regards
Maxim Received on Fri Jun 08 2007 - 18:07:28 CDT
![]() |
![]() |