I am currently writing a code for a database that simulates the work of a library.

I have a table that describes the borrowing of books. The table has entries like this:

I am supposed to create a view which only gives back the books that are free.

I have written this block of code for the view:

create view free_books as
select book_name
from book_borrowing
where date_borrowed < date_returned 
and date_returned is not null
with check option

When I select all from the table book_borrowing the expected output is:

But the output that I am getting is:

I tried to first put it only to have one row with each book name so there are no two rows with the same book name but that has only been a temporary solution as I need to allow a single book to be entered into the table multiple times for every time it is borrowed. I thought that maybe it was the not null condition that needed to be added but it still looks at the rows before it.

How do I only get the last time that 'Animal Farm' is entered into the table so I can get the expected output?

CodePudding user response：

You (probably) have a table of all books in your library, in which case use it - however you can simulate that from your sample data using the following CTE.

Then it's simply a case of checking that a book does not exist in the Borrowing table where its returned date is NULL (ie it's still borrowed):

with Books as (
    select distinct book_name
    from Borrowing
)
select Book_Name 
from Books b
where not exists (
    select * from Borrowing r
    where r.book_name = b.book_name 
      and r.date_returned is null
);

If you have a proper Books table you would do the correlation using its ID (Primary key) since you may have several copies of popular books which you'd need to track by ID, not by Name.

CodePudding user response：

OK, first of all that date_returned is not null is not necessary; if date_returned is null then the date_borrowed < date_returned will fail and those records will be excluded anyway.

But the main thing here is to add a correlated subquery to consider only the most recent date_borrowed record. So you need to change your Where clause to

Where date_borrowed=
    (Select max(date_borrowed)
     From book_borrowing x
     Where book_borrowing.book_name=x.book_name
    ) and date_borrowed < date_returned

You should also use an alias on your outer book_borrowing table reference and use it inside the subquery.