CODEWITHSUNDEEP
mysqlsql
stasisOo
stasisOo

Reputation: 133

MySQL finding the employee that made the most money out of 2 departemnts

OK this is kind of 2 questions in one, so be prepare for some abstract stuff.

I'm trying to find out of 2 tables (Dep1 and Dep2) that are connected to Employees, which may have different positions with associated salaries (for the sake of simplicity lets assume there are no associative tables in between them even though some associations will appear in the example). First question, if i wanna know who was the employee that mad the most money:

SELECT name, salary
FROM Emp NATURAL JOIN Dep1emp
HAVING salary >= ALL (SELECT salary
FROM Emp, Dep1emp
WHERE Emp.did = Dep1emp.did);

This would be enough if there were no other positions, but since there can be, how can i sum them? Example:

Name       Title     Salary
John      Writter     1k
John     Publisher    2k

And after this is solved, need to add Dep2 (from which our friend John can also be a part off.) and determine who earned the most.

The idea i had was to SELECT x FROM (SELECT ... ) but it didn't work. At all.

Thanks for all the help you can give me!

Upvotes: 0

Views: 128

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think you want a union all and aggregation. Your data structure is a bit unclear, but something like this will get the employee name with the largest sum of salaries in the two tables:

select name, sum(salary) as total_salary
from ((select name, salary from dep1emp) union all
      (select name, salary from dep2emp)
     ) e
group by name
order by total_salary desc
limit 1;

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

As this smacks of homework I'll give you a little nudge in the correct direction - you can work out the rest.

To find the employee(s) with the highest salary you could use something like the following:

SELECT *
  FROM EMPLOYEE
  WHERE SALARY = (SELECT MAX(SALARY)
                    FROM EMPLOYEE);

Best of luck.

Upvotes: 1

Related Questions