Search code examples
sqljoin

Query only use join operation

I have a database like the image below DB description

The question is: Question

My query is:

SELECT DISTINCT(book.bookdescID),book.title,book.subtitle,book.edition,book.place
FROM book
LEFT JOIN book_copy
ON book.bookdescID = book_copy.bookdescID
LEFT JOIN borrow_copy
ON book_copy.bookID = borrow_copy.bookID
WHERE borrow_copy.bookID IS NULL

The problem is that there are books which have a version of a copy that has already been borrowed, while another version of the copy hasn't been borrowed yet. My query still considers that the book has never been borrowed before and that result is false. Can anyone provide a solution for this? same book, different book copy, one copy already borrowed by people, the other have not be borrowed by anyone

Solution

  • Personally, I don't like such tasks where you are asked to write a query in a way you should not write it.

    Anyway, combine book_copy and borrow_copy in a subquery (aka derived table) and outer join this data set, so as to get a NULL only when a book has no match in there.

    SELECT b.*
FROM book b
LEFT JOIN 
(
  SELECT *
  FROM book_copy bkc
  JOIN borrow_copy brc ON brc.bookid = bkc.bookid
) bb ON bb.bookdescid = b.bookdescid
WHERE bb.bookdescid IS NULL;