I do not know what your try to achieve, but NOT EXIST is same as LEFT JOIN with all left NULL value selected
i see your mysql sql return right result , because of outer_vehicle_id may not match vehicle_id, that make vehicle_id = NULL, if you hard code vehicle_id = 433, then that means vehicle_id has value hence not selected by whole query. I hope my understanding is right. why not try LFTE JOIN, it more easy to understand. On Dec 17, 1:43 pm, Craig Boxall <[email protected]> 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] -~----------~----~----~----~------~----~------~--~---
