MySQL - joining a table to itself / sub queries-CodePudding

I'm asking for help on an assessment question I recently got wrong, I've tried a number of solutions and think I kind of know what I'm trying to do, but can't seem to figure out the syntax.

I have a table that looks like the below but with more records.

MyTable
ID   Name            DivisionID   ManagerID   Salary
123  John Smith      100          789         40000
456  Harold Johnson  101          null        60000
789  Vicky Brown     100          null        80000

and have to select the row of the person with the 3rd highest salary, which I had no problem with. However, I also need to return, instead of ManagerID, the Manager Name, which needs to be looked up from the same table.

I've tried the following solution which seems to be a bit inelegant and has to have the same query hard-coded within it, so not ideal for scaling or general use:

SELECT 
table.ID, 
Name,
DivisionID,
(SELECT 
    Name FROM table WHERE id=(
        SELECT ManagerID FROM table ORDER BY Salary DESC LIMIT 2,1)
) AS ManagerName,
Salary

FROM table
ORDER BY Salary DESC LIMIT 2,1;

I think there may be some way of doing this with subqueries, e.g. first selecting a separate table within the query of just manager id and name, and then selecting from this - but I just can't seem to get the syntax right or get my head around it. I think it might also be possible with table aliases where I select two different results from the same table under different aliases and then join the two, but again just can't figure out how to do this. Below is what I've tried to do with aliases

SELECT 
a.ID, 
a.Name,
a.DivisionID,
b.Name AS ManagerName
a.Salary

FROM table a
INNER JOIN table b ON a.ManagerID=b.ID
ORDER BY Salary DESC LIMIT 2,1;

CodePudding user response：

First of all, when asked to return the nth greatest/least value, you must ask back what to do in case of ties. They want the person with third highest salary, so with salaries 1000, 1000, 900, 900, 800, 800, 700, 600, 500, I'd suppose you want to return the persons that earn 800, because that is the third highest salary. If you just order the persons by salary, skip two and take the third, then you pick one of the persons with a salary of 900 arbitrarily, and 900 is not even the third highest, but the second highest salary.

In order to get the manager, simply join the table again. You should use an outer join for the case that an employee with the third highest salary is a manager themselves.

The straight-forward solution is to rank the rows with DENSE_RANK:

select *
from
(
  select t.*, dense_rank() over (order by salary desc) as rnk
  from mytable t
) employee
left join mytable manager on manager.id = employee.managerid
where employee.rnk = 3;

MySQL supports DENSE_RANK since version 8. In older versions you must look up the same table again. Select the distinct salaries and use your limit/offset clause on those saleries.

select *
from mytable employee
left join mytable manager on manager.id = employee.managerid
where employee.salary =
(
  select distinct salary
  from mytable
  order by salary desc
  limit 2, 1
);

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6b17e369fcd4f99ddc6c268de15f08a1

CodePudding user response：

Maka a subquersy form you code for the thrd and join selj join the table

This works also in MySQL 5.7 and earlier

CREATE TABLE MyTable (
  `ID` INTEGER,
  `Name` VARCHAR(14),
  `DivisionID` INTEGER,
  `ManagerID` VARCHAR(4),
  `Salary` INTEGER
);

INSERT INTO MyTable
  (`ID`, `Name`, `DivisionID`, `ManagerID`, `Salary`)
VALUES
  ('123', 'John Smith', '100', '789', '40000'),
  ('456', 'Harold Johnson', '101', 'null', '60000'),
  ('789', 'Vicky Brown', '100', 'null', '80000');

SELECT m1.ID, m1.Name, m1. DivisionID,m2. Name, m1.Salary 
FROM (SELECT `ID`, `Name`, `DivisionID`, `ManagerID`, `Salary` FROM MyTable ORDER BY Salary DESC LIMIT 2,1) m1 JOIN MyTable m2 on m1.ManagerID = m2.ID

 ID | Name       | DivisionID | Name        | Salary
--: | :--------- | ---------: | :---------- | -----:
123 | John Smith |        100 | Vicky Brown |  40000

db<>fiddle here