Struggling with SQL query

user_10667703 0
Viewed 163

I'm fairly new to SQL and I'm working on an assignement for my Uni database course. The request is to find the name of the employees that earn the minimum wage for each of the departments (jobs) in my database. The EMPLOYEES table contains name, code, job and wage for every employee.

This is the query I've written so far, and while it gives me all the right names, it throws in some more that shouldn't be there. My idea was to catch the minimum wage for each job (with the subquery, which actually seems to work fine), and then join that with the full EMPLOYEES table, to grab the names aswell. What am I doing wrong?

    SELECT E.EMP_NAME
    FROM EMPLOYEES AS E
    INNER JOIN (SELECT MIN(WAGE)AS W
            FROM EMPLOYEES
            GROUP BY JOB)AS EMP
    ON E.WAGE=EMP.W
    ORDER BY E.JOB;
min select join mysql sql
1 Answers

You are only joining on the wage. You need to join on the job as well:

SELECT E.EMP_NAME
FROM EMPLOYEES E JOIN
     (SELECT E2.JOB, MIN(E2.WAGE) AS MIN_WAGE
      FROM EMPLOYEES E2
      GROUP BY E2.JOB
     ) w
     ON E.WAGE = W.MIN_WAGE AND E.JOB = W.JOB
ORDER BY E.JOB;

Otherwise, you will get employees that match the minimum for another job -- not what you want.

Some notes:

  • You are learning SQL so you should give every table an alias and use it for all column references.
  • You should give meaning aliases to columns. W is not very meaningful.

Personally, I would write this using a correlated subquery:

select e.*
from employees e
where e.wage = (select min(e2.wage) from employees e2 where e2.job = e.job);
user_11440350
Related