Hi Craig,
I made a mistake with the aliases. Firstly, I don't think an alias is
needed if there is no ambiguity. So the following will work:
SELECT intClient_ID
FROM tblclients
WHERE NOT EXISTS (
SELECT intAppt_Client_ID
FROM tblappt_clients
WHERE intClient_ID = *tblclients*.intClient_ID
);
Secondly, if an alias is needed, it works when applied to the outer
table/query, not the field.
So this will work (provided an alias to tblclients):
SELECT intClient_ID
FROM tblclients *AS clients*
WHERE NOT EXISTS (
SELECT intAppt_Client_ID
FROM tblappt_clients
WHERE intClient_ID = *clients.intClient_ID*
);
but this won't (provided an alias to intClient_ID):
SELECT intClient_ID *AS outer_client_id*
FROM tblclients
WHERE NOT EXISTS (
SELECT intAppt_Client_ID
FROM tblappt_clients
WHERE intClient_ID = *outer_client_id*
);
The LEFT JOIN approach will work fine but sometimes you will want to use
EXISTS instead of NOT EXISTS and your outer dataset will splay by all
the records on the table you are left or inner joining to (which you can
correct for with DISTINCT, or GROUP BY etc but why not keep it simple).
Back to your query (which I have simplified further):
SELECT *
FROM vehicle
WHERE vehicle_type_id=1
AND vehicle_brand_id=1
AND is_active = 1
AND 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 = vehicle.id
);
Does that work? And does it run fast enough?
All the best, Grant
Grant Paton-Simpson wrote:
> 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]
-~----------~----~----~----~------~----~------~--~---
