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)) 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] -~----------~----~----~----~------~----~------~--~---
