CODEWITHSUNDEEP
mysql
JVMX
JVMX

Reputation: 1078

Ordering Records by the contents of Related Table

I have two tables. One for Salesman and the other for Sales I wish to order the output of the query by the ALPHA sort of the Name of the salesman.

Here is the Table structure:

CREATE  TABLE `Salesman` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Name` VARCHAR(45) NULL ,
  PRIMARY KEY (`ID`) );


INSERT INTO `Salesman` (`Name`) VALUES ('Bill');
INSERT INTO `Salesman` (`Name`) VALUES ('John');
INSERT INTO `Salesman` (`Name`) VALUES ('Dave');
INSERT INTO `Salesman` (`Name`) VALUES ('Mark');


CREATE  TABLE `Sales` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Item` INT VARCHAR(45) NULL ,
  `Salemesman_ID` INT NULL ,
  `Total` INT NUll ,
  PRIMARY KEY (`ID`) );

INSERT INTO `Sales` (`Item`, `Salemesman_ID`, `Total`) VALUES ('Pen', '3', '14');
INSERT INTO `Sales` (`Item`, `Salemesman_ID`, `Total`) VALUES ('Rat', '1', '12');
INSERT INTO `Sales` (`Item`, `Salemesman_ID`, `Total`) VALUES ('Car', '2', '1230');
INSERT INTO `Sales` (`Item`, `Salemesman_ID`, `Total`) VALUES('Rabbit', '2', '11');
INSERT INTO `Sales` (`Item`, `Salemesman_ID`, `Total`) VALUES('Towel', '1', '6');
INSERT INTO `Sales` (`Item`, `Salemesman_ID`, `Total`) VALUES('VaporWare', '4','900');

Because the Names in Salesmen in Alpha order are: Bill, Dave, John, Mark

I need the output to be

Rat         1  12              (Bill = 1)
Towel       1  6               (Bill = 1)
Pen         3  14              (Dave = 3)
Car         2   1230           (John = 2)
Rabbit      2    11            (John = 2)
VaporWare   4    900           (Mark = 4)

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do what you want by joining the tables together:

select s.*
from sales s join
     SalesMan sm
     on s.Salesman_Id = sm.Id
order by sm.name, sm.id;

I feel the need to add that the table name "Salesman" seems unnecessarily restrictive to only half the human race.

Upvotes: 4

Related Questions