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