Check if a set of keys exists in a result set
In a simplified rental system, there are three relations:
Book: id, title, description
Copy: id, book_id
Booking: id, copy_id, rent_from, rent_until
For each book there exist many copies. If a customer rents a book, an
available copy is selected and a corresponding booking record is created.
Now my problem: A customer selects a book, he/she wants to rent. The
system needs to check, which copies are available for the given rental
period.
What is the most elegant and fastest way to query the available copies?
Basically, step-by-step one would do the following:
Select all copies of the corresponding book
Select all bookings in the specified period of those copies
Check which copies of the book are not included in the selected bookings.
If all copies are included, the book is not available for rent in the
given period
Step 3 gives my a headache. How to do this in SQL? Can I probably even
perform all three steps in one elegant query?
No comments:
Post a Comment