Combining 2 SQL statements into 1

Question

I want to select employees who earns more than their managers. I have these SQL statements I wrote below, but how exactly would I combine these to make one statement?

SELECT E.Salary
FROM Employee E
WHERE E.ManagerId = E.Id

SELECT *
FROM Employee M
WHERE M.Salary > E.Salary AND M.ManagerId != M.Id

Your first query returns salaries of people who manage themselves, which is not a part of selecting "employees who earns more than their managers". Your second query is not a valid SQL statement since E is undefined. PS See [this](http://stackoverflow.com/a/33952141/3404097) re querying. — philipxy, Oct 14 '16 at 17:25

score 3 · Answer 1 · edited Oct 10 '16 at 20:29

3

SELECT E.Salary, M.*
FROM Employee E
 inner join Mamanger M on E.ManagerId = M.Id and E.Salary > M.Salary

edited Oct 10 '16 at 20:29

RiggsFolly

93,638
21
103
149

answered Oct 10 '16 at 20:28

Sparrow

2,548
1
24
28

score 2 · Answer 2 · edited Oct 10 '16 at 20:30

2

you could use a self inner join

SELECT E.*
FROM Employee E
INNER JOIN Employee M ON  E.ManagerId = M.Id
WHERE E.Salary > M.Salary

edited Oct 10 '16 at 20:30

RiggsFolly

93,638
21
103
149

answered Oct 10 '16 at 20:27

ScaisEdge

131,976
10
91
107

score 1 · Answer 3 · edited Oct 10 '16 at 20:30

1

Read about joining, for your example:

SELECT E.* 
FROM Employee E 
    JOIN Employee M ON E.ManagerId = M.Id 
WHERE E.Salary>M.Salary;

edited Oct 10 '16 at 20:30

RiggsFolly

93,638
21
103
149

answered Oct 10 '16 at 20:27

mjpolak

721
6
24

Combining 2 SQL statements into 1

3 Answers3