SQL script for join with self and change value-CodePudding

I have a table like this

What should I do to get output like this

I try with this but, I cant get the others value

SELECT A.UserName AS UserName, B.UserName AS Parent
FROM test A, test B
WHERE A.UserName <> B.UserName
AND (B.ID = A.Parent)

Results

CodePudding user response：

Always use explicit join, and you need to use left join:

select t1.id,
       t1.UserName , 
       t2.UserName ParentUsername
from tablename t1
left join tablename t2
    ON t1.parent = t2.ID

CodePudding user response：

you can use left outer join this query works fine

SELECT A.UserName AS UserName, B.UserName AS Parent
FROM test A left outer join test B on (B.ID = A.Parent);

it worked for me with MySQL db.

CodePudding user response：

Try a join statement:

SELECT A.UserName UserName, 
       B.UserName Parent
  FROM test A
  JOIN test B
    ON A.parent = B.ID