CODEWITHSUNDEEP
sql
wiredlime2015
wiredlime2015

Reputation: 123

SQL inner join show only unique Drivers

Learning, be kind.

Right now, this statement is showing the drivers and the vehicle id's they have used for these trips. how do I make this show only which drivers have used more than one vehicle rather than a list of vehicle ID and which driver used it? (does that make sense?):

SELECT DISTINCT s.vehicleID, o.FirstName + ' ' + o.LastName AS Driver
FROM Operators AS o, Runs AS r, Schedules AS s JOIN Trips AS t
ON s.scheduleID = t.scheduleID 
WHERE r.BidDate >= '09/01/2004' AND r.BidDate <= '09/30/2004'

Upvotes: 0

Views: 63

Answers (4)

Gabriel Ste-Marie Beauchamp
Gabriel Ste-Marie Beauchamp

Reputation: 41

SELECT COUNT(DISTINCT s.vehicleID) AS Count, o.FirstName + ' ' + o.LastName AS Driver
FROM Operators AS o
JOIN Runs AS r ON o.ID = r.OperatorID
JOIN Schedules AS s ON r.ScheduleID = s.ScheduleID
WHERE r.BidDate BETWEEN '09/01/2004' AND '09/30/2004' 
GROUP BY o.FirstName + ' ' + o.LastName
HAVING COUNT(DISTINCT s.vehicleID) > 1

Upvotes: 1

Bayeni
Bayeni

Reputation: 1046

You can use GROUP BY clause:

SELECT o.FirstName + ' ' + o.LastName AS Driver, COUNT(s.vehicleID) NoOfVehicleUsed
FROM Operators AS o, Runs AS r, Schedules AS s JOIN Trips AS t
ON s.scheduleID = t.scheduleID 
WHERE r.BidDate >= '09/01/2004' AND r.BidDate <= '09/30/2004'
GROUP BY o.FirstName + ' ' + o.LastName
HAVING COUNT(s.vehicleID) > 1

Upvotes: 0

Royi Namir
Royi Namir

Reputation: 148694

I don't really know what's the purpose of each table , but assuming you have table (map_driver_To_vehicle) with : 

driverId , vehicleID

which represents that

  • driverid 5 took vehicleId 1
  • driverid 2 took vehicleId 5
  • etc...

You can do : 

select driverId , cnt=count(distinct vehicleID )
from map_driver_To_vehicle
group by driverid
having count(distinct vehicleID ) >1

nb : you said more then one vehicle. So I presume you mean different car and not used more then one - the same car.

Either way you can remove the distinct.

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

My best guess not knowing the foreign key field names in your tables

SELECT
    o.FirstName + ' ' + o.LastName AS Driver,
    COUNT(s.vehicleID) AS VehicleCount
FROM
    Operators AS o
    JOIN Runs AS r ON o.operatorID = r.operatorID
    JOIN Schedules AS s ON r.runsID = s.runsID
WHERE 
    r.BidDate >= '09/01/2004'
    AND r.BidDate <= '09/30/2004'
GROUP BY 
    o.FirstName + ' ' + o.LastName
HAVING 
    COUNT(s.vehicleID) > 1

Upvotes: 0

Related Questions