Hi Craig,
I meant rows not row - the inner query should return a recordset.
IIRC you need to make it a correlated subquery so that there is a link
between the inner and outer queries:
e.g. (NB a general example, not specific to your problem)
SELECT client_id AS outer_client_id
FROM tblclients
WHERE EXISTS (
SELECT item_id
FROM tblitems
WHERE item_date < '2008-01-01'
AND client_id = outer_client_id
);
The key part is the
AND ClientID = outerclientid
so that the inner query only creates the inner query for that client,
not for all clients. This syntax is very powerful and easy to
understand once you're familiar with. I came across it in a book on SQLite.
All the best, Grant
Grant Paton-Simpson wrote:
> Quick reply - use EXISTS or NOT EXISTS
>
> e.g.
>
> SELECT .....
> FROM .....
> WHERE EXISTS (your SQL in here which returns row for an item if it has
> any bookings within the timeframe)
>
>
>
> Craig Boxall 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]
-~----------~----~----~----~------~----~------~--~---
