5

I have a table like

Employee
==================
name      salary
==================
a        10000
b        20000
c        5000
d        40000

i want to get all the employee whose salary is greater than A's salary. I don't want to use any nested or sub query. It has been asked in an interview and hint was to use self join. I really can't figure out how to achieve the same.

krock
  • 28,904
  • 13
  • 79
  • 85
Mohit Vashistha
  • 1,824
  • 3
  • 22
  • 49

2 Answers2

16
select e1.* from Employee e1, Employee e2  where 
           e2.name = 'a' and
           e1.salary > e2.salary

Using self join

 select e1.* from Employee e1 join Employee e2  on 
           e2.name = 'a' and
           e1.salary > e2.salary
Salil
  • 46,566
  • 21
  • 122
  • 156
1
SELECT emp1.* FROM Employee emp1 JOIN Employee emp2
ON emp2.Name = 'A' and emp1.Salary > emp2.Salary
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Chinjoo
  • 2,697
  • 6
  • 28
  • 45
  • this will work. only modification will be mysql are case sensitive so emp2.Name = 'A' should be emp2.Name = 'a' – Salil Jun 14 '10 at 05:52