CODEWITHSUNDEEP
sqlsql-serverself-joincorrelated-subquery
aprkturk
aprkturk

Reputation: 85

Self Join with Correlated Subquery

I need to find an employee with a salary greater than their Manager. For this, I used to following query and it works;


    SELECT 
        e1.EmpID
        ,e1.EmpName
        ,e1.EmpSalary
        ,e1.ManagerID
    FROM empsalary e
    INNER JOIN empsalary e1 ON e.EmpID = e1.ManagerID
    WHERE e1.EmpSalary > e.EmpSalary

But following one is not working. I want to know why it is not? Why it's result is null? What should be correct format? 

SELECT * 
FROM empsalary as e
WHERE e.empsalary=(
                   SELECT e1.empsalary 
                   FROM empsalary as e1 
                   WHERE e.EmpID = e1.ManagerID 
                   AND e1.EmpSalary > e.EmpSalary)

Sample data and code here;

EmpID       EmpName    EmpSalary            ManagerID
----------- ---------- -------------------- -----------
1           Neevan     100000               6
2           Mukesh     30000                6
3           Disha      50000                6
4           Martin     90000                6
5           Roy        170000               6
6           Anvesh     168000               NULL



CREATE TABLE empsalary
(
    EmpID INT
    ,EmpName VARCHAR(10)
    ,EmpSalary BIGINT
    ,ManagerID INT 
)

INSERT INTO empsalary
VALUES
(1,'Neevan',100000,6)
,(2,'Mukesh',30000,6)
,(3,'Disha',50000,6)
,(4,'Martin',90000,6)
,(5,'Roy',170000,6)
,(6,'Anvesh',168000,NULL)

Upvotes: 1

Views: 3230

Answers (2)

Joe
Joe

Reputation: 626

There's no need for me to re-write how to do it, as Gordon has done a good enough job already, but I can explain why yours returned null...

For me to answer this, I needed to re-write yours slightly to help me to read it. It is essentially the same as yours. Also, similarly to how Gordon has done, I've substituted e for m and e1 for e. I've also called your tables tblSalaries, so that the table names aren't the same as the column name:

SELECT * 
FROM tblSalaries as m
WHERE m.empsalary=(
                   SELECT e.empsalary 
                   FROM tblSalaries as e 
                   WHERE e.ManagerID = m.EmpID
                   AND e.EmpSalary > m e.EmpSalary)

If we work backwards and interpret the last part firstly:

...
(
 SELECT e.empsalary 
 FROM tblSalaries as e 
 WHERE e.ManagerID = m.EmpID
 AND e.EmpSalary > m.EmpSalary)

Firstly, WHERE m.ManagerID = e.EmpID is saying 'Find all employees in e who's manager ID is the same as the employee ID in m'. Considering there is only one managerID (6), then records 1-5 from e will all match the manager record, on m (6) on this.

Your next clause AND e.EmpSalary > m.EmpSalary is finding those who's salary is greater than the managers'. Therefore, you are left with record 5 (Roy) as you intended.

Now to return to your main query:

SELECT * 
    FROM tblSalaries as m
    WHERE m.empsalary= (...tblSalaries as e...)

We have established that table e in the brackets has returned Roy, but we have also established that it only matches records in table m, where m is a manager. Ultimately, you are asking then to find where the manager's salary = Roy's salary; the answer, null.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271161

Your correlations are all backwards. The right way is:

SELECT e.* 
FROM empsalary e
WHERE e.empsalary > (SELECT m.empsalary 
                     FROM empsalary  m
                     WHERE m.EmpID = e.ManagerID 
                    );

Notice that I have used the m table alias for the manager record. This helps to follow the logic.

Upvotes: 2

Related Questions