CODEWITHSUNDEEP
sql-server
Saurav Kundu
Saurav Kundu

Reputation: 9

How to get column of table with a self-referencing foreign key?

I have a table structure shown below:

Id      Name    Sal     ManagerId
1       a       5000       2
2       b       7000       3
3       c       6000       1

I need output like this

Id     Name    Sal     Manager
1      a       5000      b
2      b       7000      c
3      c       6000      a

How can i do that?

Upvotes: 0

Views: 257

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

You have to use a self-JOIN to link one table to itself:

SELECT t1.Id, t1.Name, t1.Sal, t2.ManagerName AS Manager
FROM TableName t1 INNER JOIN TableName t2 
    ON t1.ManagerID = t2.Id

If ManagerId is nullable you might want to use an OUTER JOIN:

SELECT t1.Id, t1.Name, t1.Sal, COALESCE(t2.ManagerName, '<no manager>') AS Manager
FROM TableName t1 LEFT OUTER JOIN TableName t2 
    ON t1.ManagerID = t2.Id

Upvotes: 2

Related Questions