Hi all.
I have to do an a/v resources reservation application with the following
normalized data model:
Categories
category
name
Inventory
item
category
Reservation
id
date
time_from
time_until
Reservation_details
id
item
Categories holds items in major groups as in 'laptops',
'data projectors', 'data displays' and so on.
Inventory holds the actual items for each category: 9 laptops, 3
projectors, 2 data displays and so on.
Reservation and Reservation_details should be obvious.
When an user makes a reservation he chooses from the various categories
without knowing how many items are in each one.
What the application needs to validate is the stock of items for each
chosen category for the date and time chosen by the user.
For example, if the user wants a laptop computer the application
would have to show the message "sorry, there are no laptops available
for the date and time you wanted" if in fact all of the laptops are
checked out for use, but it would have to provide the application the
list of all the items for the laptop category still in stock at that
moment.
Now, I've tried succesfully using the following select query:
select Inventory.item,Inventory.category,
Reservation_details.item,Reservation_details.id,
Reservation.date from Inventory
left join Reservation_details on Reservation_details.item =
Inventory.item
left join Reservation on Reservation_details.id = Reservation.id
where Inventory.category = "user-chosen-category"
AND Reservation_details.id IS NULL;
The problem comes when I try to add date and time selection criteria:
select Inventory.item,Inventory.category,
Reservation_details.item,Reservation_details.id,
Reservation.date from Inventory
left join Reservation_details on Reservation_details.item =
Inventory.item
left join Reservation on (Reservation_details.id = Reservation.id
AND Reservation.date='user_chosen_date')
where Inventory.category = "user_chosen_category"
AND Reservation_details.id IS NULL;
I get the same results as with the select without the date filtering.
What am I doing wrong?
Is this even the right way to approach the problem?
I've tried many variations on the subject and so far I don't know if
I'm even on the right track to solve that puzzle.
Any input would be greatly appreciated...
Thanks in advance.
Jorge Gomez
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
