Search code examples
mysqlinner-join

Create table using inner join from the table itself

I've this Table:

CREATE TABLE `employees` (
  `EMPLOYEE_ID` decimal(6,0) NOT NULL DEFAULT '0',
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(25) NOT NULL,
  `EMAIL` varchar(25) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `HIRE_DATE` date NOT NULL,
  `JOB_ID` varchar(10) NOT NULL,
  `SALARY` decimal(8,2) DEFAULT NULL,
  `COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
  `MANAGER_ID` decimal(6,0) DEFAULT NULL,
  `DEPARTMENT_ID` decimal(4,0) DEFAULT NULL,
  PRIMARY KEY (`EMPLOYEE_ID`),
  UNIQUE KEY `EMP_EMAIL_UK` (`EMAIL`),
  KEY `EMP_DEPARTMENT_IX` (`DEPARTMENT_ID`),
  KEY `EMP_JOB_IX` (`JOB_ID`),
  KEY `EMP_MANAGER_IX` (`MANAGER_ID`),
  KEY `EMP_NAME_IX` (`LAST_NAME`,`FIRST_NAME`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I need to create a query to find the (employee_id, LAST_NAME,FIRST_NAME, manager ID and the salary) of the lowest-paid employee for every manager_id.

I've tried a lot of solution but none doesn't work.

I've tried:

    concat(m.MANAGER_ID, ', ', min(m.salary)) as Manager,
    concat(e.EMPLOYEE_ID, ', ', e.LAST_NAME, ', ', e.FIRST_NAME) as Employe
    from employees as m
    INNER JOIN employees as e on e.MANAGER_ID = m.MANAGER_ID and e.salary = m.salary
    group by m.MANAGER_ID order by min(m.salary) desc;

That return the right manager_id and the min(salary), but return the fist employee_id, LAST_NAME,FIRST_NAME he finds with that manager_id

I've also tried:

Select e.EMPLOYEE_ID, e.LAST_NAME, e.FIRST_NAME
from employees as e 
inner join (select MANAGER_ID, min(salary)
from employees 
group by MANAGER_ID order by min(salary) desc) as m 
on m.manager_id = e.manager_id and m.salary = e.salary;

but return Error Code: 1054. Unknown column 'm.salary' in 'on clause' why it see e.manager_id but Unknown column 'm.salary'?

thank you in advance for your help.

Solution

  • Your second attempt is very close.

    1. You need to provide a column alias for min(salary) in your subquery so you have something to refer to that column by in your main SQL.
    2. You don't want to join on min(salary) since each line in your result set is to have the salary of the employe making the least amount of money for the manager to which that record's employee reports to.
    3. You'll want that min(salary) in your main SELECT clause so it's present in your final result set.
    Select e.EMPLOYEE_ID, e.LAST_NAME, e.FIRST_NAME, m.min_salary
from employees as e 
  inner join (
     select MANAGER_ID, min(salary) as min_salary
     from employees 
     group by MANAGER_ID 
  ) as m 
  on m.manager_id = e.manager_id;

    I also removed the ORDER BY in your subquery since, at best, it's superfluous and at worst it will throw an error.