Sergei Basharov Sergei Basharov - 19 days ago 5
SQL Question

Return all rows with IDs that are not present in another table

I have a row entity, say

Book
, in table
Library
. I also have a table
Borrowings
with rows representing
Borrowing
entity.

Book
has a primary key which is used in a
Borrowing
row to mark a book as borrowed in field
book_id
. There can be 0-n
Borrowing
rows that can be connected to a single
Book
.


  • If there are no
    Borrowing
    rows for a specific book, then the
    Book
    is available.

  • If there are 1-n
    Borrowing
    rows for a specific book with
    return_date
    less than current date, then the
    Book
    is available.

  • There can be only 1
    Borrowing
    row with
    return_date
    in the future. If this one is present, the
    Book
    is borrowed and not available for readers.



My question is - how do I find all books that are available for borrowing because they fulfill any of these criteria?


  • Have no
    Borrowing
    rows with their ID referenced.

  • Have no
    Borrowing
    rows with
    return_date
    in the future.


Answer

Try below query

SELECT *
FROM library l
LEFT JOIN borrowing b 
 ON l.book_id = b.book_id
WHERE (b.book_id IS NULL
     OR NOW()::date - b.return_date::date > 0) 

Hope this should help you out.