sql join values from same table

i came up by visiting this question.

so i made a query like this

SELECT ru.iUserId,ru.iRefUserId,CONCAT(ru.vName,ru.vLastName) AS userName,CONCAT(iru.vName,iru.vLastName) AS RefUserName
FROM `register_user` ru
 INNER JOIN `register_user` iru
 ON ru.iRefUserId = iru.iRefUserId
WHERE ru.iRefUserId > 0

And Database is like this So basically my need is i need an refUserId User Full Name When i am querying like

SELECT * FROM TABLE WHERE iRefUserId > 0

Above Query Result output me same name as the userName

Answer

You are probably looking for an outer join: Show all users along with their ref user, if such exists.

SELECT
  ru.iuserid,
  ru.irefuserid,
  CONCAT(ru.vname, ru.vlastname) AS username,
  CONCAT(iru.vname, iru.vlastname) AS refusername
FROM register_user ru
LEFT OUTER JOIN register_user iru ON iru.iuserid = ru.irefuserid
ORDER BY ru.iuserid;

I’ve corrected the erroneous join condition (wrong ID). Moreover, as there exists no user with user ID 0, and I guess you just meant to avoid looking that ID up, I dropped the condition ru.iRefUserId > 0. It is not needed, because the outer join handles this already.