HOw to get Query result with sub query-CodePudding

I have a table like below

ID      LOCATION    PARENTID
445632  SPND0237    0
521847  SPND0237A   445632

I NEED OUTPUT LIKE BELOW

ID      LOCATION    PARENT_NAME PARENTID
445632  SPND0237    NULL            0
521847  SPND0237A   SPND0237        445632

I am using the below query

select a.ID, a.LOCATION, b.LOCATION as PARENT_NAME, a.PARENTID
from table as a
left join table as b on a.PARENTID = b.ID

But not getting results.

CodePudding user response：

create table #emp (
ID int primary key,
LOCATION varchar(100),
PARENTID int
)
insert into #emp values (445632  ,'SPND0237', 0)
insert into #emp values (521847  ,'SPND0237A', 445632)
--select * from #emp

select a.ID, a.LOCATION, B.LOCATION AS PARENT_NAME, A.PARENTID
from #emp a left join #emp b on A.PARENTID = B.ID

your given query works, there is no issue with that