How to view number of available books in a library DB?

[Andreianu Georgiana]

Hi everyone!

I have to write a query based off a library DB to output number of available books for each title. I have a table with book information including the total number of books for each title and a table with the lendings and returnings of each book. If a book has no return date then it is not in the library at the moment and the 'available' number goes down by one.

Now I'm having problems with writting this query, i have tried to use CASE for when the id for the book matches in both tables and the return date is null then the 'available' number is total-1 else it's the total number but it never decreases, the avaible is equal to the equal.

I don't know if this makes the point of this forum, but I have run out of ideas for this query. Please help, at least some ideas and directions of how to do this.

Thanks in advance!