Subquery using 3 Tables SQL

Question

I'm trying to display the last name of the lowest paid employees from each city. The city column falls under a table titled LOCATIONS while employee information(salary, last name) falls under EMPLOYEES. Both of these tables are related share no common table, so I have to rely on a third table, DEPARTMENTS to connect the two as DEPARTMENTS contains a department_id that it shares with EMPLOYEES as well as a LOCATION_ID that it shares with LOCATIONS. This is what I have so far, but I'm having trouble with this as I've mostly worked with only two tables in the past.

SELECT LAST_NAME
FROM EMPLOYEES
WHERE (DEPARTMENT_ID) IN
(SELECT DEPARTMENT_ID
FROM DEPARTMENTS
WHERE LOCATION_ID IN
(SELECT LOCATION_ID
FROM LOCATIONS
GROUP BY CITY
HAVING MIN(SALARY)));

Answer 1

This seems to be an assignment in an intro course in SQL. So let's assume you can't use analytic functions, match_recognize clause, etc. Just joins and aggregates.

In the subquery in the WHERE clause below, we compute the min salary for each city. We need to join all three tables for this. Then in the overall query we join the three tables again, and we use the subquery for an IN condition (a semi-join). The overall query looks like this:

select e.last_name
from   employees e join departments d 
                     on e.department_id = d.department_id
                   join locations   l
                     on d.location_id   = l.location_id
where  ( e.salary, l.city ) in 
       (
         select   min(salary), city
         from     employees e join departments d 
                                on e.department_id = d.department_id
                              join locations   l
                                on d.location_id   = l.location_id
         group by city
       )
;

Answer 2

You should separate out the concept of table joins from WHERE clauses. Use WHERE for filtering data, use JOIN for connecting data together.

I think this is what you are wanting. By the way, lose the ALL CAPS if you can.

SELECT
    LAST_NAME
FROM
    EMPLOYEES
    INNER JOIN (
        SELECT
            DEPARTMENTS.DEPARTMENT_ID, 
            CITY,
            MIN(SALARY) AS LOWEST_SALARY
        FROM
            EMPLOYEES
            INNER JOIN DEPARTMENTS ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
            INNER JOIN LOCATIONS ON DEPARTMENTS.LOCATION_ID = LOCATIONS.LOCATION_ID
        GROUP BY
            DEPARTMENTS.DEPARTMENT_ID,
            LOCATIONS.CITY
    ) AS MINIMUM_SALARIES
    ON EMPLOYEES.DEPARTMENT_ID = MINIMUM_SALARIES.DEPARTMENT_ID
       AND EMPLOYEES.SALARY = MINIMUM_SALARIES.LOWEST_SALARY

Answer 3

First of all join the tables, so you see city and employee in one row. If we group by city we get the minimum salary per city.

with city_employees as
(
  select l.city, e.*
  from locations l 
  join departments d using (location_id)
  join employees e using (department_id)
)
select last_name 
from city_employees 
where (city, salary) in
(
  select city, min(salary)
  from city_employees
  group by l.city
);

It is easier to achieve the same, however, with window functions (min over or rank over here).

select last_name
from
(
  select 
    e.last_name,
    e.salary,
    min(e.salary) over (partition by l.city) as min_salary
  from locations l 
  join departments d using (location_id)
  join employees e using (department_id)
)
where salary = min_salary;