Craig Boxall:
> Big help needed with a rather simple yet complex sql query! Many hours
> have been wasted already!
>
> the sql needs to check availability of an item based on whether it has
> any bookings against it for the given time frame.
>
> In pseudo-speak, we want the following:
>
> select all the items where there are no bookings or the bookings are
> outside the given timeframe or bookings in the timeframe have a status
> less than 2.
>
> we have successfully eliminated all items that have a booking but we
> cant seem to make it differentiate between firm bookings (status=2)
> and enquiries (status=1)
>
> so we have this working so far:
>
> SELECT item.* FROM item LEFT JOIN booking ON item.id=booking.item_id
> WHERE ((booking.id IS NOT NULL) OR (booking.end_date < $start_date OR
> booking.start_date > $end_date))
>
SELECT item.*
FROM item
LEFT JOIN booking ON item.id=booking.item_id
WHERE
(ISNULL(booking.id))
-- there are no bookings
OR
(NOT ISNULL(booking.id) AND
(booking.end_date < $start_date OR booking.start_date > $end_date))
-- the bookings are outside the given timeframe
OR
(NOT ISNULL(booking.id) AND booking.status < 2 AND
(booking.end_date BETWEEN $start_date AND $end_date OR
booking.endstart_BETWEEN $start_date AND $end_date))
-- bookings in the timeframe have a status less than 2
Not sure what you mean by "bookings in the timeframe"
> this will return all items that have either no bookings or they do
> have bookings but the start and end dates of the timeframe we are
> looking for do not intersect.
>
> but we run into trouble when we try and say something like ' or
> booking.status =1'. The problem is if we have an enquiry AND a firm
> booking in that timeframe, it always returns the vehicle because it
> fulfils the criteria of the booking.status=1 and ignores the fact
> there is a glaring conflict with another booking. :(
>
> If anyone is keen to help out on this one and needs any more details,
> let me know. Any help is appreciated.....
>
> <incentive> Wouldnt say no to paying for a working solution in some
> way....? </incentine>
>
> Cheers
> Craig
>
> ---------------------------------------------------
> Craig Boxall
>
> [e] [email protected]
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
-~----------~----~----~----~------~----~------~--~---
