Hi Craig,
I think you can simplify the query quite a bit which is one of the
benefits of using NOT EXISTS. For examples, I don't see why you need
any sort of join to booking at all in the main outer query. Which also
means you won't need DISTINCT. It is simply a list of vehicle records
and presumably the vehicle.id is unique (one per row in vehicle). The
WHERE statement is the important bit. You want all vehicles where type
=1m brand = 1, active, not sold AND not in the subquery. So here is a
possible revision:
SELECT vehicle.*, vehicle.id AS outer_vehicle_id
FROM vehicle
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 id FROM booking
WHERE booking_status_id >= 4
AND (
(start_date > '2008-12-01' AND start_date < '2008-12-31')
OR
('2008-12-01' BETWEEN start_date AND end_date)
)
AND vehicle_id = outer_vehicle_id
);
Apologies if I haven't understood the date clauses but hopefully you get
the general idea.
All the best, Grant
Craig Boxall wrote:
> 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]
-~----------~----~----~----~------~----~------~--~---
