What i am trying to do is select a vehcile from tblGSAVehicles based on its location, vehcile type and that it is not in use in tblSchedule. The problem is that I can limit it to one vehicle but it always pick it no matter if it is scheduled for those dates . How can I limit it to only if its not scheduled for a start date or end date in tblschedule. The code below I thought would work.
So with code below it pulls up the same vehicle 3 times I put in a distinct and this goes away, but at this point I am confused and to why the vehicle is being pulled when its scheduled in tblSchedue and shoould pull nothing.
FROM tblGSAVehicles AS v LEFT JOIN
tblSchedule AS s ON v.intVehicleID = s.intVehicleID INNER JOIN
tblVehicleType AS t ON v.intVehicleTypeID = t.intVehicleTypeID
WHERE v.intLocID = 1 AND
t.intVehicleTypeID = 4 AND
v.bitInUse = 1 AND NOT EXISTS (Select * from tblschedule where
((Convert(varchar(10), s.dtStartDate, 101) NOT BETWEEN '11/23/2010' AND '11/25/2010') OR s.dtStartDate IS NULL) AND
((Convert(varchar(10), s.dtStartDate, 101) NOT BETWEEN '11/23/2010' AND '11/25/2010') OR s.dtEndDate IS NULL))