| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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.
On Jun 8, 5:56 pm, Albright.Br..._at_gmail.com wrote:
> 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?
Posting the table definitions and insert statements is a significant help (note to future posters).
Maxim has provided a solution that produces the desired list. Let's
see if we can develop another method to solve this problem. First, a
simple experiment using the analytical version of COUNT:
SELECT
B.BOOKTITLE,
B.AUTHOR,
COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) NUM_BOOKS
FROM
BOOKS B
WHERE
B.AUTHOR='J.K Rowling';
BOOKTITLE AUTHOR NUM_BOOKS -------------------- -------------------- ---------- Prisoner J.K Rowling 2 Goblet J.K Rowling 2
Not too impressive yet, but let's add in the second table:
SELECT
BC.MEMBERNAME,
B.BOOKTITLE,
B.AUTHOR,
COUNT(DISTINCT B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR)
AUTHOR_NUM_BOOKS,
COUNT(BC.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME, B.AUTHOR)
MEMBER_NUM_BOOKS
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE(+);
Note that I changed the original COUNT(B.BOOKTITLE) to COUNT(DISTINCT
B.BOOKTITLE) and changed the alias to AUTHOR_NUM_BOOKS:
MEMBERNAME AUTHOR_NUM_BOOKS MEMBER_NUM_BOOKS
-------------------- ---------------- ---------------- Chuck 2 2 Mary 2 1 Mike 2 2 Mike 2 2 Chuck 2 2
Now, we need a way to first eliminate all rows where AUTHOR_NUM_BOOKS is not equal to MEMBER_NUM_BOOKS, and then return a list of names without duplicates. This can be accomplished by sliding the above SQL statement into an inline view:
SELECT DISTINCT
MEMBERNAME
FROM
(SELECT
BC.MEMBERNAME,
COUNT(DISTINCT B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR)
AUTHOR_NUM_BOOKS,
COUNT(BC.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME, B.AUTHOR)
MEMBER_NUM_BOOKS
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE(+))
WHERE
AUTHOR_NUM_BOOKS=MEMBER_NUM_BOOKS;
MEMBERNAME
Let's try again, this time without analytical functions. First, let's
find out how many of the author's books were read by each membername:
SELECT
BC.MEMBERNAME,
B.AUTHOR,
COUNT(*) MEMBER_NUM_BOOKS
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND BC.BOOKTITLE=B.BOOKTITLE
GROUP BY
BC.MEMBERNAME,
B.AUTHOR;
MEMBERNAME AUTHOR MEMBER_NUM_BOOKS
-------------------- -------------------- ---------------- Mike J.K Rowling 2 Chuck J.K Rowling 2 Mary J.K Rowling 1
Now, let's determine the number of books written by each author:
SELECT
AUTHOR,
COUNT(*) AUTHOR_NUM_BOOKS
FROM
BOOKS
GROUP BY
AUTHOR;
AUTHOR AUTHOR_NUM_BOOKS
-------------------- ---------------- Rocksteady 1 Brian 1 J.K Rowling 2
Let's put each into an inline view and pull out the membernames of
interest:
SELECT DISTINCT
BC.MEMBERNAME
FROM
(SELECT
BC.MEMBERNAME,
B.AUTHOR,
COUNT(*) MEMBER_NUM_BOOKS
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND BC.BOOKTITLE=B.BOOKTITLE
GROUP BY
BC.MEMBERNAME,
B.AUTHOR) BC,
(SELECT
AUTHOR,
COUNT(*) AUTHOR_NUM_BOOKS
FROM
BOOKS
GROUP BY
AUTHOR) B
WHERE
B.AUTHOR=BC.AUTHOR
AND B.AUTHOR_NUM_BOOKS=BC.MEMBER_NUM_BOOKS;
MEMBERNAME
Let's try one more time, A simple starting point:
SELECT
BC.MEMBERNAME,
COUNT(BC.BOOKTITLE)
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE(+)
GROUP BY
BC.MEMBERNAME;
MEMBERNAME COUNT(BC.BOOKTITLE)
-------------------- ------------------- Chuck 2 Mary 1 Mike 2
[The above does not need to be an outer join]
Now, let's add an inline view to retrieve the total number of books
written by the author:
SELECT
BC.MEMBERNAME,
COUNT(BC.BOOKTITLE) MEMBER_NUM_BOOKS,
NB.AUTHOR_NUM_BOOKS
FROM
BOOKS B,
BOOKCAMP BC,
(SELECT
COUNT(*) AUTHOR_NUM_BOOKS
FROM
BOOKS
WHERE
AUTHOR='J.K Rowling') NB
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE(+)
GROUP BY
BC.MEMBERNAME,
NB.AUTHOR_NUM_BOOKS;
MEMBERNAME MEMBER_NUM_BOOKS AUTHOR_NUM_BOOKS
-------------------- ---------------- ---------------- Chuck 2 2 Mike 2 2 Mary 1 2
The final clean up is accomplished with a HAVING clause:
SELECT
BC.MEMBERNAME
FROM
BOOKS B,
BOOKCAMP BC,
(SELECT
COUNT(*) AUTHOR_NUM_BOOKS
FROM
BOOKS
WHERE
AUTHOR='J.K Rowling') NB
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE(+)
GROUP BY
BC.MEMBERNAME,
NB.AUTHOR_NUM_BOOKS
HAVING
COUNT(BC.BOOKTITLE)=NB.AUTHOR_NUM_BOOKS;
MEMBERNAME
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Jun 08 2007 - 20:27:24 CDT
 |
 |