Hi all..... testing NOT EXISTS at the moment... will let you know how we get on....
Cheers for all the feedback thus far! Craig On 17/12/2008, at 11:59 AM, William Buick wrote: > > I think this query will do what you want > > SELECT > item.* > FROM > item > LEFT JOIN > booking > ON > item.id = booking.item_id > WHERE > booking.id IS NULL > OR ( > booking.id IS NOT IN ( > SELECT > b.id > FROM > booking AS b > WHERE > b.end_date < $start_date > OR > b.start_date > $end_date > ) > OR > booking.id < 2 > ) > > Cheers, > > William. > > On Wed, Dec 17, 2008 at 11:38 AM, Craig Boxall <[email protected] > > wrote: >> >> 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] -~----------~----~----~----~------~----~------~--~---
