| 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 9, 9:09 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Charles Hooper schrieb:
> > 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....
> >
> > 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
> > --------------------
> > Chuck
> > Mike
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Nice examples.
> Just for fun, yet another one:
>
> SELECT MEMBERNAME
> FROM (SELECT B.MEMBERNAME,CAST(COLLECT(booktitle) AS
> SYS.dbms_debug_vc2coll) BOOKLIST
> FROM BOOKCAMP B
> GROUP BY MEMBERNAME) M,
> (SELECT AUTHOR,CAST(COLLECT(booktitle) AS
> SYS.dbms_debug_vc2coll) BOOKLIST
> FROM BOOKS B
> GROUP BY AUTHOR) A
> WHERE A.BOOKLIST SUBMULTISET OF M.BOOKLIST
> AND AUTHOR = 'J.K Rowling'
> /
>
> Best regards
>
> Maxim
Interesting, I am fairly certain that I would not have found that solution. The above solution seems to run a little slow on my system, but it is still interesting.
Let's see if there is another way - caution, this might be
inefficient:
The starting point:
SELECT
B.AUTHOR,
B.BOOKTITLE,
ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE)
BOOK_NUM,
COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
FROM
BOOKS B
WHERE
AUTHOR='J.K Rowling';
AUTHOR BOOKTITLE BOOK_NUM BOOK_COUNT
-------------------- -------------------- ---------- ---------- J.K Rowling Goblet 1 2 J.K Rowling Prisoner 2 2
Now, let's put the book list into a comma separated list:
SELECT
SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
(SELECT
B.AUTHOR,
B.BOOKTITLE,
ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE)
BOOK_NUM,
COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
FROM
BOOKS B
WHERE
B.AUTHOR='J.K Rowling')
WHERE
BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
BOOK_NUM=BOOK_NUM-1
START WITH
BOOK_NUM=1;
BOOK_LIST
We are now half way done. Prepare to do the same with the BOOKCAMP
table:
SELECT
BC.MEMBERNAME,
BC.BOOKTITLE,
ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY
B.BOOKTITLE) BOOK_NUM,
COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR)
BOOK_COUNT
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE;
MEMBERNAME BOOKTITLE BOOK_NUM BOOK_COUNT
-------------------- -------------------- ---------- ---------- Chuck Goblet 1 2 Chuck Prisoner 2 2 Mary Goblet 1 1 Mike Goblet 1 2 Mike Prisoner 2 2
Generate a comma separated list for each MEMBERNAME:
SELECT
MEMBERNAME,
SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
(SELECT
BC.MEMBERNAME,
BC.BOOKTITLE,
ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY
B.BOOKTITLE) BOOK_NUM,
COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR)
BOOK_COUNT
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE)
WHERE
BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
(MEMBERNAME||TO_CHAR(BOOK_NUM))=(MEMBERNAME||TO_CHAR(BOOK_NUM-1))
START WITH
BOOK_NUM=1;
MEMBERNAME BOOK_LIST
Chuck Goblet,Prisoner Mary Goblet Mike Goblet,Prisoner
Now, let's put it all together to see where the author book list
matches the MEMBERNAME book lists:
SELECT
BC.MEMBERNAME
FROM
(SELECT
SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
(SELECT
B.AUTHOR,
B.BOOKTITLE,
ROW_NUMBER() OVER (PARTITION BY B.AUTHOR ORDER BY B.BOOKTITLE)
BOOK_NUM,
COUNT(B.BOOKTITLE) OVER (PARTITION BY B.AUTHOR) BOOK_COUNT
FROM
BOOKS B
WHERE
B.AUTHOR='J.K Rowling')
WHERE
BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
BOOK_NUM=BOOK_NUM-1
START WITH
BOOK_NUM=1) B,
(SELECT
MEMBERNAME,
SUBSTR(SYS_CONNECT_BY_PATH(BOOKTITLE,','),2) BOOK_LIST
FROM
(SELECT
BC.MEMBERNAME,
BC.BOOKTITLE,
ROW_NUMBER() OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR ORDER BY
B.BOOKTITLE) BOOK_NUM,
COUNT(B.BOOKTITLE) OVER (PARTITION BY BC.MEMBERNAME,B.AUTHOR)
BOOK_COUNT
FROM
BOOKS B,
BOOKCAMP BC
WHERE
B.AUTHOR='J.K Rowling'
AND B.BOOKTITLE=BC.BOOKTITLE)
WHERE
BOOK_NUM=BOOK_COUNT
CONNECT BY PRIOR
(MEMBERNAME||TO_CHAR(BOOK_NUM))=(MEMBERNAME||TO_CHAR(BOOK_NUM-1))
START WITH
BOOK_NUM=1) BC
WHERE
B.BOOK_LIST=BC.BOOK_LIST;
MEMBERNAME
Oddly, the above executes much faster than the CAST(COLLECT(booktitle) AS SYS.dbms_debug_vc2coll) solution. Maybe the dataset size should be increased, and the OP should post the performance results of each method to see how the first two solutions compare with the others. I think that it would be interesting to see if the CAST(COLLECT(booktitle) AS SYS.dbms_debug_vc2coll) method scales better than the other methods.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sat Jun 09 2007 - 09:35:32 CDT
 |
 |