x

Query returning the record not needed

I have 2 tables. one for vehicle and another for Driver. The vehicles assigned to a driver will be saved in the Driver table. The vehicle can be unassigned to him.

Conditions for assigning a vehicle to a Driver: 1. It should not be assigned to any driver. 2. If the vehicle is assigned to a driver and later unassigned, it can be reassigned to the same driver or any other.

This is my Select Query.

 SELECT  dbo.Vehicle.Vehicle_ID AS ADPUFLEETNOTASSIGNED_ID ,
         dbo.Vehicle.REGNUMBER ,
         dbo.Vehicle.MAKE ,
         dbo.Vehicle.MODEL ,
         dbo.DRIVERDETAIL.Vehicle_ID ,
         dbo.DRIVERDETAIL.RETURNED ,
         driverdetail.DRIVER
 FROM    dbo.Vehicle
         LEFT OUTER JOIN dbo.DRIVERDETAIL ON dbo.Vehicle.Vehicle_ID = dbo.DRIVERDETAIL.Vehicle_ID
 WHERE   CASE WHEN dbo.DRIVERDETAIL.Vehicle_ID IS NULL THEN 1
              WHEN dbo.DRIVERDETAIL.Vehicle_ID IS NOT NULL
                   AND DRIVERDETAIL.RETURNED IS NOT NULL THEN 1
              ELSE 0
         END = 1

Here DateReturned is set when unassigning a vehicle.

Now my issue is, if i assign a vehicle to Driver A and unassign it, a record exists with the DriverID with DateReturned. So when assigning to Driver B, this record is fetched based on the above query. Now i assign to Driver B the same vehicle. Another Record is inserted into the Driver table without Date Returned. Now when i try to assign for Driver C, this vehicle should not show up. Instead it shows up on the screen, because the condition is not satisfied.

Please provide me the neccesary query to address this issue.

more ▼

asked Mar 05, 2014 at 07:50 AM in Default

avatar image

jennifer 2
22 2 3 6

is DRIVERDETAIL.RETURNED the date field or some kind of indicator (true/false)?

Mar 05, 2014 at 08:58 AM Kev Riley ♦♦

what logic is used when assigning/unassigning a vehicle? Are you always adding a new row, or updating existing rows?

Mar 05, 2014 at 09:14 AM Kev Riley ♦♦

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.

Mar 08, 2014 at 10:52 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I think your query is listing all available vehicles, in which case it should first list all those which have no entry in the DriverDetail table. Then it should list all vehicles for which all the entries have been returned. (DRIVERDETAIL.Returned IS NOT NULL). This is best done with two queries, the outer join you've got (three of columns will be blank anyway) UNIONed with a an INNER JOIN GROUP BY Vehicleid that filters out all the vehicles with null DRIVERDETAIL.Returned entries (that are still out). (something like having count(*) = sum(case when DRIVERDETAIL.Returned IS NOT null then 1 else 0 end)

OK, it looks more complicated but it should be easier to understand and maintain!

more ▼

answered Mar 05, 2014 at 06:30 PM

avatar image

Phil Factor
4.2k 8 23 21

(comments are locked)
10|1200 characters needed characters left

Thank you all for ur replies. Finally I used this in my WHERE clause.

WHERE
NOT EXISTS
( SELECT 1 FROM DRIVERDETAIL WHERE DRIVERDETAIL.VEHICLE_ID = VEHICLE.VEHICLE_ID)
OR EXISTS
( SELECT 1 FROM DRIVERDETAIL WHERE DRIVERDETAIL.VEHICLE_ID = VEHICLE.VEHICLE_ID AND DRIVERDETAIL.RETURNED IS NOT NULL
AND NOT EXISTS
( SELECT 1 FROM DRIVERDETAIL WHERE DRIVERDETAIL.VEHICLE_ID = VEHICLE.VEHICLE_ID AND DRIVERDETAIL.RETURNED IS NULL) )

more ▼

answered Mar 06, 2014 at 06:55 AM

avatar image

jennifer 2
22 2 3 6

(comments are locked)
10|1200 characters needed characters left
 WHERE    CASE     WHEN     dbo.DRIVERDETAIL.Vehicle_ID IS NULL 
         THEN     1 
         WHEN     dbo.DRIVERDETAIL.Vehicle_ID IS NOT NULL 
         AND     DRIVERDETAIL.RETURNED IS NOT NULL 
         AND    DRIVERDETAIL.DateReturned IS NOT NULL
         THEN     1 
         ELSE     0 
         END     = 1
more ▼

answered Mar 05, 2014 at 08:47 AM

avatar image

Squirrel
2.7k 1 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x157

asked: Mar 05, 2014 at 07:50 AM

Seen: 523 times

Last Updated: Mar 08, 2014 at 10:52 AM

Copyright 2017 Redgate Software. Privacy Policy