CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ravinder Bhawer
Ravinder Bhawer

Reputation: 3

Return name of the employee who having top salary with join

here is the situation. I have two tables, one is table Tbl_employ, second is tbl_details.

Tbl_employ
----------
id  | name 
1   | Ravi
2   | ram 
3   | sham
4   | john

Tbl_details
-----------
id   | salary   | emp_id
1    | 500      | 1
2    | 200      | 2 
3    | 400      | 3
4    | 501      | 4

I want to return the name of the employee who has top salary in tbl_detail.

What will be the join query for this condition?

Please suggest. Thanks in advance.

Upvotes: 0

Views: 166

Answers (2)

AB Vyas
AB Vyas

Reputation: 2389

I appreciate the answer of @Max Vernon. You can also do it by another way. Please try this

select t.name from (
select Distinct top 1 salary ,name
from Tbl_employ as E
left outer join Tbl_details as D on D.empid=E.id
order by salary desc
 ) as t

you can check it here SQL Fiddle

Upvotes: 0

Hannah Vernon
Hannah Vernon

Reputation: 3472

Perhaps:

SELECT TOP(1) name
FROM Tbl_employ e INNER JOIN Tbl_details d ON e.id = d.emp_id
ORDER BY d.salary DESC;

Essentially, this joins the two tables on the key fields (id and emp_id), returning only a single result (TOP(1)) that is the maximum salary row (ORDER BY d.salary DESC).

Upvotes: 2

Related Questions