0

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.

  1. SAD_Driver

    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
    );
    
  2. SAD_DriverAssignment

    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)
    );
    
  3. SAD_Stop

    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)
    );
    
  4. SAD_Location

    CREATE TABLE SAD_Location (
      latitude  DECIMAL(19,16),
      longitude     DECIMAL(19,16),
      locName   VARCHAR(40),
      CONSTRAINT OK_Location PRIMARY KEY (latitude, longitude)
    );
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.