Query to comapre scalar value of one table with all values of another column in same table

Question

for ex

empid  empname  mngrid
1         A       3
2         B       3
3         C       1

I want output tbl like below

empname mngrname
A          C
B          C
C          A

Possible duplicate of [Explanation of self-joins](http://stackoverflow.com/questions/2458519/explanation-of-self-joins) — Felix Pamittan, Nov 03 '15 at 06:12
Your hierarchy is actually a loop? Well that's an interesting company... — James Z, Nov 03 '15 at 18:48

score 0 · Answer 1 · answered Nov 03 '15 at 06:19

You can use LEFT JOIN, like this:

SELECT A.empname, B.empname AS mngrname
FROM Your_Table A
LEFT JOIN You_Table B
ON A.mngrid = B.empid

Other case, you can use OUTER APPLY, like this:

SELECT A.empname, B.mngrname
FROM Your_Table A
OUTER APPLY (SELECT empname AS mngrname You_Table WHERE empid = A.mngrid) B

1 Answers1