Hi all,
That looked like the perfect solution although it doesnt seem to be
working for us. Here is the exact sql we are calling:
SELECT DISTINCT vehicle.*, vehicle.id AS outer_vehicle_id
FROM vehicle LEFT JOIN booking ON vehicle.id=booking.vehicle_id
WHERE
vehicle.vehicle_type_id=1
AND
vehicle.vehicle_brand_id=1
AND
vehicle.is_active = 1
AND
vehicle.is_sold = 0
AND
NOT EXISTS(SELECT * FROM booking
WHERE
booking_status_id >= 4
AND
(
('2008-12-01' < start_date and '2008-12-31' >
start_date)
OR
('2008-12-01' BETWEEN start_date AND end_date)
)
AND
vehicle_id = outer_vehicle_id
)
To give you an idea of where we are having the issue,
we have a booking with the following data:
id vehicle_id booking_status_id start_date
end_date
1 433 4
2008-12-10 2008-12-20
when we get our list of vehicles we still get the vehicle with the id
433 in the list.
EVEN THOUGH.....
when you run the select inside the NOT EXISTS, it does what we expect
(if we replace vehicle_id = outer_vehicle_id with vehicle_id = 433)
So my question is, can you use selected column alias's inside the
subselect? After doing some research Im thinking not....
Suggestions?
Cheers
On 17/12/2008, at 11:53 AM, Grant Paton-Simpson wrote:
>
> 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]
-~----------~----~----~----~------~----~------~--~---
