CODEWITHSUNDEEP
sqloracle-database
Elio
Elio

Reputation: 73

Borrowers who borrowed ONLY all books of a specified author and editor

For a given author and editor, i need to list names of borrowers who borrowed "ONLY" "ALL" the books of that author and editor

I tried the following: 

SELECT m.name
FROM  MEMBER m,COPIES c,BORROW b1,BOOK b,AUTHOR a,EDITOR e,WRITE w
WHERE m.mid=b1.mid AND b1.copyid=c.copyid AND c.bookid=b.bookid AND b.editor=e.edcode AND a.authorcode=w.author AND w.book=b.bookid 
MINUS
(SELECT m.name
FROM  MEMBER m,COPIES c,BORROW b1,BOOK b,AUTHOR a,EDITOR e,WRITE w
WHERE m.mid=b1.mid AND b1.copyid=c.copyid AND c.bookid=b.bookid AND b.editor=e.edcode AND a.authorcode=w.author AND w.book=b.bookid AND (a.authorcode<>:P58_AUTHOR OR e.edcode<>:P58_EDITOR)    
)

It gave me a member who borrowed at least one book and didnt borrow any book other than that author and editor , so I need to make sure he borrowed all the books

Upvotes: 0

Views: 700

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The following returns all the books by a given author and editor:

select w.bookid
from book b join
     writes w 
     on b.bookid = w.bookid
where b.edcode = :P58_EDITOR and
      w.authorcode = :P58_AUTHOR;

The following finds members who have borrowed all these books:

with bae as (
      select w.bookid
      from book b join
           writes w 
           on b.bookid = w.bookid
      where b.edcode = :P58_EDITOR and
            w.authorcode = :P58_AUTHOR 
     )
select b.mid
from borrow b join
     copies c
     on c.copyid = b.copyid join
     bae
     on b.bookid = bae.bookid
group by b.mid
having count(distinct b.bookid) = (select count(*) from bae);

Note that the count(distinct) is needed because a member could, presumably, borrow the same book twice.

Upvotes: 1

Boneist
Boneist

Reputation: 23578

I think the following will give you what you need:

WITH book_ed_auth_dets AS (SELECT b.bookid,
                                  a.authorcode,
                                  e.edcode,
                                  COUNT(*) OVER (PARTITION BY a.authorcode, e.edcode) tot_num_bks_per_auth_ed
                           FROM   book b
                                  INNER JOIN WRITE w ON b.bookid = w.book
                                  INNER JOIN author a ON w.author = a.authorcode
                                  INNER JOIN editor e ON b.editor = e.edcode
                           WHERE  a.authorcode = :p58_author
                           AND    e.edcode = :p58_editor)
SELECT m.name
FROM   MEMBER m
       INNER JOIN borrow b1 ON m.mid = b1.mid
       INNER JOIN copies ON c.bookid ON b1.copyid = c.copyid
       INNER JOIN book_ed_auth_dets bead ON c.bookid = bead.bookid
GROUP BY m.id,
         m.name,
         bead.authorcode,
         bead.edcode
         bead.tot_num_bks_per_auth_ed
HAVING   COUNT(DISTINCT bead.bookid) = bead.tot_num_bks_per_auth_ed);

N.B. Untested, since you didn't provide sample data to work with.

This finds the books for a given author and editor, and uses an analytic count to write out the total number of books for that author and editor for each row.

Then we inner join the member's borrowed books to that, find the count of distinct bookids (in case the member borrowed the same book more than once) and only report members if that count matches the count of books for the author and editor.

Upvotes: 3

Popeye
Popeye

Reputation: 35900

If I am not wrong then WRITE table is the most imp table which you missed to describe.

I have taken an idea from your existing query and tried to create the solution as follows:

SELECT
    NAME
FROM
    (
        SELECT
            M.NAME,
            -- NO OF BOOK BORROWED BY MEMBER OF THAT AUTHOR AND EDITOR
            COUNT(DISTINCT B.BOOKID) NO_BOOKS_BORROWED 
        FROM
            MEMBER M
            JOIN BORROW B1 ON ( M.MID = B1.MID )
            JOIN COPIES C ON ( B1.COPYID = C.COPYID )
            JOIN BOOK B ON ( C.BOOKID = B.BOOKID )
            JOIN EDITOR E ON ( B.EDITOR = E.EDCODE )
            JOIN WRITE W ON ( A.AUTHORCODE = W.AUTHOR )
            JOIN AUTHOR A ON ( W.BOOK = B.BOOKID )
        GROUP BY
            M.NAME
        --CONDITION TO CHECK THAT BORROWER HAS NOT BOUGHT ANY OTHER BOOK
        HAVING 
            SUM(CASE
                WHEN A.AUTHORCODE <> :P58_AUTHOR
                     OR E.EDCODE <> :P58_EDITOR THEN 1
            END) = 0
    ) BORRROWER
    --
    JOIN (
        SELECT
            -- NO OF ALL BOOKS WRITTEN BY AUTHOR AND EDITOR
            COUNT(DISTINCT B.BOOKID) NO_BOOKS_WRITTEN
        FROM
            EDITOR E
            JOIN WRITE W ON ( A.AUTHORCODE = W.AUTHOR )
            JOIN AUTHOR A ON ( W.BOOK = B.BOOKID )
        WHERE
            A.AUTHORCODE = :P58_AUTHOR
            AND E.EDCODE = :P58_EDITOR
    ) AUTHOR_EDITOR ON ( BORRROWER.NO_BOOKS_BORROWED = AUTHOR_EDITOR.NO_BOOKS_WRITTEN )

Cheers!!

Upvotes: 1

Related Questions