getting this query to do what its supposed to do

user_6345963 0
Viewed 65

So, I think the question only wants the Member who have actually RENTED/CHECK OUT ALL THE BOOK in either currently or in the past with book title "Harry Potter". If there is a member who only check out in either current or history 2 out of the 4 books it shouldn't output that member and if that member only check out 2 out of 4 books in just current it shouldn't output that either. But if a member currently or had check out all the book with title "Harry Potter" it should out that member regardless if all the books were currently check out or was in the past. But if all the books were check out just current or just in the past it should output that member.

Here is the question:

List the memberID, firstname, and lastname of the members who have borrowed either currently or in the past all the books in the library with “Harry Potter” in the title. If any of such books have multiple copies, the member must have borrowed at least one copy of each of such books.

Code:

CREATE TABLE Book
    (bookID INT,
    ISBN INT,
    title varchar (60),
    author  varchar (20),
    publish_year INT,
    category varchar(20),
    PRIMARY KEY (bookID));  

CREATE TABLE Member
    (memberID INT,
    lastname varchar (20),
    firstname varchar (20),
    address varchar(20),
    phone_number INT,   
    limit_ INT,
    PRIMARY KEY (memberID));

CREATE TABLE CurrentLoan
    (memberID INT ,
    bookID INT,
    loan_date DATE,
    due_date DATE,
    PRIMARY KEY (memberID, bookID),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));

CREATE TABLE History
    (memberID INT,
    bookID INT,
    loan_date DATE,
    return_date DATE,
    PRIMARY KEY (memberID, bookID, loan_date),
    FOREIGN KEY (memberID) REFERENCES Member(memberID),
    FOREIGN KEY (bookID) REFERENCES Book(bookID));

INSERT INTO Book VALUES (10, 7771452369, 'XML and XQuery', 'Author Le', 2017, 'reference');
INSERT INTO Book VALUES (11, 8881245525, 'XQuery: The XML Query Language', 'Jack Se', 2017, 'reference');
INSERT INTO Book VALUES (12, 9991123546, 'Yellow Bird', 'Jake Red', 2014, 'reference');
INSERT INTO BOOK VALUES (13, 1212121212, 'The Giving Tree', 'Shel Silverstein', 1964, 'fiction');
INSERT INTO BOOK VALUES (14, 2121212121, 'Gone Fishing', 'Shel Silverstein', 1964, 'reference');
INSERT INTO BOOK VALUES (15, 1313131313, 'The Lazy Dog', 'Jake Red', 2016, 'childrens');
INSERT INTO BOOK VALUES (16, 3131313131, 'The Red Bird', 'Jake Red', 2016, 'childrens');
INSERT INTO BOOK VALUES (17, 1414141414, 'The Very Blue Boy', 'Ben Jen', 2006, 'fiction');
INSERT INTO Book VALUES (18, 1113312336, 'Harry Potter 1', 'J. K. Rowling', 2000, 'fiction');
Insert INTO Book VALUES (19, 1113331142, 'Harry Potter 1', 'J. K. Rowling', 2000, 'fiction');
INSERT INTO Book VALUES (20, 2221257787, 'The Real Harry Potter 2', 'J. K. Rowling', 2009, 'fiction');
INSERT INTO Book VALUES (21, 2221254896, 'The Fake Harry Potter 3', 'J. K. Rowling', 2010, 'fiction');
INSERT INTO Book VALUES (22, 2221254896, 'The Fake Harry Potter 3', 'J. K. Rowling', 2010, 'fiction');
INSERT INTO Book VALUES (23, 2221254896, 'The Fake Harry Potter 4', 'J. K. Rowling', 2012, 'fiction');
INSERT INTO Book VALUES (24, 2221254896, 'The Fake Harry Potter 4', 'J. K. Rowling', 2012, 'fiction');

INSERT INTO Member VALUES (001, 'Lee', 'Nancy', 'Brownlea Drive', 1254896325, 10);
INSERT INTO Member VALUES (002, 'Le', 'Ray', '10th Street', 1234561256, 2);
INSERT INTO Member VALUES (003, 'Kan', 'Charlie', '5th Street', 1234567236, 8);
INSERT INTO Member VALUES (004, 'Brown', 'Joe', 'Elm Street', 1234567845, 9);
INSERT INTO Member VALUES (005, 'Smith', 'John', '33 East', 1234567890, 3);
INSERT INTO Member VALUES (006, 'Khang', 'Nkaujyi', '358 Spencer', 2145345625, 5);

INSERT INTO CurrentLoan VALUES (001, 10, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (001, 11, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (001, 18, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (001, 20, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (001, 22, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (001, 24, '13-SEP-17', '14-NOV-17');
INSERT INTO CurrentLoan VALUES (002, 21, '14-FEB-17', '12-MAR-17');
INSERT INTO CurrentLoan VALUES (002, 23, '14-FEB-17', '12-MAR-17');

INSERT INTO History VALUES (002, 10, '03-JAN-16', '25-MAY-16');
INSERT INTO History VALUES (002, 11, '03-JAN-16', '25-MAY-16');
INSERT INTO History VALUES (002, 12, '03-JAN-16', '25-MAY-16');
INSERT INTO History VALUES (002, 18, '03-JAN-16', '25-MAY-16');
INSERT INTO History VALUES (002, 20, '03-JAN-16', '25-MAY-16');
INSERT INTO History VALUES (002, 13, '03-JAN-16', '25-MAY-16');
COMMIT;

This query will out memberID 2 because that memeber have all the book with title "Harry Potter" check out in either current or history. But, memberID 1 didn't and its supposed to output it since it has all the book with title "Harry Potter" in current.

Query:

SELECT Member.memberID, lastname, firstname
FROM Member
INNER JOIN CurrentLoan ON Member.memberID = CurrentLoan.memberID
INNER JOIN Book ON CurrentLoan.bookID = Book.bookID
WHERE Book.title like '%Harry Potter%'
intersect
SELECT Member.memberID, lastname, firstname
FROM Member
INNER JOIN History ON Member.memberID = History.memberID
INNER JOIN Book ON History.bookID = Book.bookID
WHERE Book.title like '%Harry Potter%';
oracle sql
2 Answers

INTERSECT will only return rwos which occur in both queries - so you will only get people who have currently got a matching book on loan AND have also taken matching books out in the past.

You want something like:

Query 1:

WITH matching_books AS (
  SELECT BookID, ISBN
  FROM   book
  WHERE  title LIKE '%Harry Potter%'
)
SELECT MemberId,
       Firstname,
       lastname
FROM   Member
WHERE  MemberID IN (
         SELECT MemberID
         FROM   ( SELECT MemberID, BookID
                  FROM   CurrentLoan
                  UNION ALL
                  SELECT MemberID, BookID
                  FROM   History
                ) l
                INNER JOIN matching_books b
                ON ( l.bookId = b.bookId )
         GROUP BY MemberID
         HAVING COUNT( DISTINCT b.ISBN ) = ( SELECT COUNT( DISTINCT isbn ) FROM matching_books )
       )

Results:

No results - this is because no user has taken out all the books with each unique ISBN for the Harry Potter books. You could redo the query to compare on title (rather than ISBN) and it will give the results you are looking for but this is not guaranteed to be unique.

user_15092640

You could use GROUP BY and HAVING to check "all" books.

WITH cte AS (
    SELECT *
    FROM Book
    WHERE  title LIKE '%Harry Potter%'
), cte2 AS (
   SELECT memberId
   FROM (SELECT l.*, b.title
         FROM currentLoan l
         JOIN Book b  ON b.bookId = l.bookId
         UNION ALL
         SELECT l.*, b.title
         FROM History l
         JOIN Book b  ON b.bookId = l.bookId) sub
   WHERE bookID IN (SELECT bookID FROM cte)
   GROUP BY memberId
   HAVING COUNT(DISTINCT(title)) = (SELECT COUNT(DISTINCT title) FROM cte)
)
SELECT memberID, firstname,  lastname
FROM Member
WHERE memberId IN (SELECT memberId FROM cte2);

Rextester Demo

EDIT:

Simpler form:

SELECT memberID, firstname,  lastname
FROM (
    SELECT  b.title, m.memberID, m.firstname,  m.lastname
    FROM currentLoan l
    JOIN Book b  ON b.bookId = l.bookId
    JOIN member m ON l.memberId = m.memberId
    WHERE b.bookID IN (SELECT bookId FROM Book WHERE title LIKE '%Harry Potter%')
    UNION ALL
    SELECT  b.title, m.memberID, m.firstname,  m.lastname
    FROM History l
    JOIN Book b  ON b.bookId = l.bookId
    JOIN member m ON l.memberId = m.memberId
    WHERE b.bookID IN (SELECT bookId FROM Book WHERE title LIKE '%Harry Potter%')
    ) sub
GROUP BY memberID, firstname,  lastname
HAVING COUNT(DISTINCT(title))= 
      (SELECT COUNT(DISTINCT title) FROM Book WHERE title LIKE '%Harry Potter%');

Rextester Demo2

user_50708790
Related