I have a database where I have driver details, their assigned routes and their stop locations. For each driver on the database I need to find the location (or locations, as it could be more than one) that the driver has stopped at most often.And I need to get the driver’s first and last name, the name of the location and corresponding number of stops and torder the result by the number of stops. So far I have come up with following query which gives all the stop locations for every route for every driver.
SELECT DISTINCT SAD_DriverAssignment.driver, SAD_DriverAssignment.route, SAD_Stop.stopNo, SAD_Driver.fName, SAD_Driver.lName FROM SAD_Stop INNER JOIN SAD_DriverAssignment ON SAD_Stop.routeNo = SAD_DriverAssignment.route INNER JOIN SAD_Driver ON SAD_DriverAssignment.driver = SAD_Driver.nisNo WHERE SAD_DriverAssignment.driver IN (SELECT SAD_Driver.nisNo FROM SAD_Driver) ORDER BY SAD_DriverAssignment.driver ASC
I am just ordering here for clarity. But when I used the COUNT() on stopNo all I get is a one result with only one driver. Can you please explain the proper way to do this.
Following are my table structures.
CREATE TABLE SAD_Driver ( nisNo CHAR(9) PRIMARY KEY, fName VARCHAR(30), lName VARCHAR(30), licenseNo CHAR(16) NOT NULL, dob DATE , categories SET ('AM','P','Q','A1','A2','A','B1','B','B+E','C','C+E','C1','C1+E'), trainer CHAR(9), CONSTRAINT Driver_Trainer FOREIGN KEY (trainer) REFERENCES SAD_Driver(nisNo) ON DELETE SET NULL ON UPDATE CASCADE );
CREATE TABLE SAD_DriverAssignment ( workday DATE, driver CHAR(9), route DECIMAL(4,0) UNSIGNED NOT NULL, vehicle CHAR(7) NOT NULL, CONSTRAINT FK_DriveDayAssign_Driver FOREIGN KEY (driver) REFERENCES SAD_Driver(nisNo) ON UPDATE CASCADE, CONSTRAINT FK_DriveDayAssign_Day FOREIGN KEY (workday) REFERENCES SAD_Workday(theDate) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FK_DriveDayAssign_Vehicle FOREIGN KEY (vehicle) REFERENCES SAD_Vehicle(regNo) ON UPDATE CASCADE, CONSTRAINT FK_DriveDayAssign_Route FOREIGN KEY (route) REFERENCES SAD_Route(routeNo) ON UPDATE CASCADE, CONSTRAINT PK_Assignment PRIMARY KEY (workday,driver), CONSTRAINT Unique_Day_Vehicle UNIQUE (workday,vehicle), CONSTRAINT Unique_Day_Route UNIQUE (workday,route) );
CREATE TABLE SAD_Stop ( routeNo DECIMAL (4,0) UNSIGNED, stopNo DECIMAL(3,0) UNSIGNED, latitude DECIMAL(19,16), longitude DECIMAL(19,16), CONSTRAINT PK_Location PRIMARY KEY (routeNo,stopNo), CONSTRAINT FK_stop_location FOREIGN KEY (latitude,longitude) REFERENCES SAD_Location(latitude,longitude), CONSTRAINT FK_stop_route FOREIGN KEY (routeNo) REFERENCES SAD_Route(routeNo) );
CREATE TABLE SAD_Location ( latitude DECIMAL(19,16), longitude DECIMAL(19,16), locName VARCHAR(40), CONSTRAINT OK_Location PRIMARY KEY (latitude, longitude) );