CODEWITHSUNDEEP
sqldatabase
adsbb pppp
adsbb pppp

Reputation: 53

SQL - SELECTING MAX returning different column

My problem is the code is the code is returning the max salary of an employee including managers when i want it to exclude managers.

Aside from that it works.

Here is the code i have written:

SELECT firstname || ' ' || lastname 
AS "FULL NAME"
FROM EMPLOYEES
WHERE salary = 
    ( SELECT MAX(salary)
        FROM EMPLOYEES, DEPARTMENTS 
        WHERE NOT employee_id = manager_id);

Here is the database schema

COUNTRIES(country_id, country_name, region) 
• Primary key: {country_id}

DEPARTMENTS(department_id, department_name, manager_id)
• Primary key: {department_id }
• Foreign key: [manager_id]  ⊆ EMPLOYEES[employee_id]

DEPT_LOCATIONS(department_id, city, country_id)
• Primary key: {department_id, city}
• Foreign key: [department_id]  ⊆ DEPARTMENTS[department_id] and 
[country_id]  ⊆ COUNTRIES[country_id]

EMPLOYEES(employee_id, firstname, lastname, email, phone_no, salary, 
department_id)
• Primary key: {employee_id}
• Foreign key: [department_id]  ⊆ DEPARTMENTS[department_id]

Any help much appreciated.

Upvotes: 0

Views: 34

Answers (2)

Hogan
Hogan

Reputation: 70513

If your subquery selects the max salary by department then you can join on that result to get your list by department.

SELECT firstname || ' ' || lastname AS "FULL NAME",
       E.department_id
FROM EMPLOYEES E
JOIN (
  SELECT E.department_id, MAX(E.salary) as MAX_SALARY
  FROM EMPLOYEES E
  JOIN DEPARTMENTS D ON E.department_id = D.department_id
  WHERE employee_id <> manager_id
  GROUP BY E.department_id 
) AS M ON M.MAX_SALARY = E.salary AND E.department_id = M.department_id

if you don't want it by department it looks like this:

SELECT firstname || ' ' || lastname AS "FULL NAME"
FROM EMPLOYEES E
JOIN (
  SELECT MAX(E.salary) as MAX_SALARY
  FROM EMPLOYEES E
  JOIN DEPARTMENTS D ON E.department_id = D.department_id
  WHERE employee_id <> manager_id
) AS M ON M.MAX_SALARY = E.salary

Upvotes: 1

Prisoner
Prisoner

Reputation: 1857

How about this?

SELECT e.firstname || ' ' || e.lastname 
AS "FULL NAME"
FROM EMPLOYEES e
WHERE e.salary = 
    ( SELECT MAX(salary)
        FROM EMPLOYEES, DEPARTMENTS 
        WHERE NOT employee_id = manager_id)
AND NOT EXISTS (SELECT 1 FROM DEPARTMENTS d WHERE e.employee_id = d.manager_id)

All you need is filter the result

Upvotes: 0

Related Questions