CODEWITHSUNDEEP
sql-server
user6132703
user6132703

Reputation:

SQL joining confusion in same table

I have a table like this

enter image description here

;WITH People AS 
(
    SELECT 1 as id, 'Ahanda' as name, 20000 as salary, 3 as manager_id
    UNION ALL
    SELECT 2, 'Mahfuj', 30000, 4
    UNION ALL
    SELECT 3, 'Mr. John', 15000, NULL
    UNION ALL
    SELECT 4, 'Mr. Tom', 18000, NULL
)

Now I want this type of output find the name of the person who has a manager and his manager's salary is more than 17000.

I have tried with this SQL

SELECT P.name 
FROM People P 
INNER JOIN People Q ON P.id = Q.manager_id
WHERE Q.salary >= 17000

But the the answer is

enter image description here

But from the values of the table it will be only row number 2 of the People table and the answer will be Mahfuj.

How can I solve this?

Upvotes: 3

Views: 64

Answers (4)

Aarush Aggarwal
Aarush Aggarwal

Reputation: 143

You need to change the query as below :

SELECT P.name FROM People P INNER JOIN People Q ON P.manager_id = Q.id WHERE Q.salary >= 17000

Upvotes: 3

gofr1
gofr1

Reputation: 15997

Just change JOIN statement:

SELECT P.name 
FROM People P 
INNER JOIN People Q 
    ON Q.id = P.manager_id
WHERE Q.salary>=17000

Output:

name
Mahfuj

Upvotes: 1

Rich Benner
Rich Benner

Reputation: 8113

Got it the wrong way round, your join has the logic backwards. You want to join the manager id from table 'P' to the id of table 'Q'

SELECT 
P.name 
FROM  People P 
INNER JOIN People Q 
ON P.manager_id = Q.id
WHERE q.salary>=17000

P.S. I love that the managers get paid less than their employees.

Upvotes: 4

StackUser
StackUser

Reputation: 5398

Try like this,

SELECT P.NAME
FROM People P
INNER JOIN People Q ON P.manager_id = Q.ID
WHERE Q.salary >= 17000

Upvotes: 1

Related Questions