This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find an employee with a salary greater than their Manager.
Input Data:
|
1 2 3 4 5 6 7 8 |
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 |
Expected Output:
|
1 2 3 |
EmpID EmpName EmpSalary ManagerID ----------- ---------- -------------------- ----------- 5 Roy 170000 6 |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE empsalary ( EmpID INT ,EmpName VARCHAR(10) ,EmpSalary BIGINT ,ManagerID INT ) GO 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) GO |
Solution 1: Use CTE
|
1 2 3 4 5 6 7 8 9 10 |
with ctetest as ( select * from empsalary ) ,cte2 as ( select *from ctetest where ManagerID is null ) select *from ctetest where EmpSalary > (select empsalary from cte2) |
Solution 2: Use Self -Join
|
1 2 3 4 5 6 7 8 |
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 |
Please try the different solution for this puzzle and share it via comment...
