15,175,307 members
1.00/5 (1 vote)
See more:
```table employee(#emp_name, city, salary);
table salary(salary,#city);
table company(#Company_name, city);

Find out the Company Name who gives Highest Salary to their Employees.```

What I have tried:

SELECT SUM(e.Salary) FROM employee AS e, salary AS s, company AS c WHERE e.city = s.city AND e.city = c.city
Posted
Updated 11-Oct-17 0:40am
Peter Leow 14-Jan-17 6:06am

When I read the title, I thought you were looking for the company that has the highest paid employee. However, your sql statement suggested that you are trying to find the company that paid the highest TOTAL amount of salary to all its employees, it that right? It doesn't make much sense. Companies with more employees tend to pay more in total as compared to those with fewer employees. What exactly do you want to achieve? Just curious.

Just kidding: Try fortune 500. :-)

## Solution 1

Since this seems to be homework, I'm not writing the complete answer...

Well basically your're close. Just add company name to the `SELECT` list and `GROUP BY` it. Then order the set using `ORDER BY` clause. Since you're looking for the highest sum, order in descending order. And the last step, fetch only the first row. For this you can use `TOP`.

However, the table design looks a bit odd. Employee does not have direct relation to Company but the relation goes via city. Also you have a separate salary table, why is that?

This is not homework, Actually I am learning SQL to prepare myself for job interview. I found this problem somewhere and try to solve it. Is the following modified query works right? Thank's for your valuable response.

SELECT Company_name, SUM(e.Salary) AS Highest_Salary FROM employee AS e, salary AS s, company AS c WHERE e.city = s.city AND e.city = c.city GROUP BY Company_name ORDER BY Highest_Salary DESC LIMIT 1
Wendelius 14-Jan-17 5:38am

Seems quite right. Depending on the database you use, it may be wrong to use AS for the table alias and some database use TOP and some LIMIT clause.

Can you please share me the right one.
Wendelius 14-Jan-17 8:14am

I mean that the query looks fine. If you have the database, just try to run it.

Ok, Thanks

## Solution 2

select company_name from company natural join salary where salary = (select max(salary) from salary);
Richard Deeming 12-Oct-17 14:21pm

If the OP is still waiting for someone to do his homework 10 months later, he's failed the course!

## Solution 3

Is city common field in all the tables? if so you can use join query for company and salary table.

Try this query, it will work

SELECT C.COMPANY_NAME,SUM(S.SALARY) FROM COMPANY_NAME C LEFT JOIN SALARY S ON C.CITY = S.CITY GROUP BY C.COMPANY_NAME ORDER BY DESC

Thanks,
Vijay
v2