sql join values from same table

Question

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

Most people here want sample table data and expected result as formatted text, not as images. And take a look at [mcve], make it easy to assist you! — jarlh, Sep 11 '19 at 07:49
i already provided the sample data that is required for understand the concern @jarlh — TarangP, Sep 11 '19 at 07:50
https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557 — jarlh, Sep 11 '19 at 07:52

score 0 · Accepted Answer · answered Sep 11 '19 at 08:11

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.

Zaynul Abadin Tuhin · Answer 2 · 2019-09-11T08:37:43.187

-1

need to change join key

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

note i have given WHERE ru.iRefUserId > 0 this codition becuase you given in your answer that's why but if you put this then you 1st will be ignored always

edited Sep 11 '19 at 08:37

answered Sep 11 '19 at 07:49

Zaynul Abadin Tuhin

31,407
5
33
63

not working ..! when i run `SELECT ru.iUserId,ru.iRefUserId, CONCAT(ru.vName,ru.vLastName) AS userName FROM `register_user` ru WHERE ru.iRefUserId > 0` than i found one data but when i run your query it return 0 results – TarangP Sep 11 '19 at 07:58
@TarangP https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0013871c7e8ad5768a411726fab1763c chcecck the link i created demo for you and its working fine before down vote please chekc 1st – Zaynul Abadin Tuhin Sep 11 '19 at 08:35
i didn't do that @Zaynul – TarangP Sep 11 '19 at 09:10

sql join values from same table

2 Answers2