INNER JOIN same table

Question

I am trying to get some rows from the same table  It s a user table  user has user id and user parent id    I need to get the user id row and user parent id row  I have coded something like this   SELECT user user fname  user user lname FROM users as user INNER JOIN users AS parent ON parent user parent id   user user id WHERE user user id     GET id    But it doesn t show the results  I want to display user record and its parent record

User · Answer

You can also use UNION like  SELECT  user fname           user lname FROM    users  WHERE   user id     GET id  UNION SELECT  user fname           user lname FROM    users  WHERE   user parent id     GET id

User · Answer

Lets try to answer this question  with a good and simple scenario  with 3 MySQL tables i e  datetable  colortable and jointable   first see values of table datetable with primary key assigned to column dateid   mysql gt  select   from datetable   -------- ------------    dateid   datevalue     -------- ------------       101   2015-01-01        102   2015-05-01        103   2016-01-01    -------- ------------  3 rows in set  0 00 sec    now move to our second table values colortable with primary key assigned to column colorid   mysql gt  select   from colortable   --------- ------------    colorid   colorvalue    --------- ------------         11   blue                12   yellow        --------- ------------  2 rows in set  0 00 sec    and our final third table jointable have no primary keys and values are   mysql gt  select   from jointable   -------- ---------    dateid   colorid    -------- ---------       101        11        102        12        101        12    -------- ---------  3 rows in set  0 00 sec    Now our condition is to find the dateid s  which have both color values blue and yellow   So  our query is   mysql gt  SELECT t1 dateid FROM jointable AS t1 INNER JOIN jointable t2     - gt  ON t1 dateid   t2 dateid     - gt  WHERE     - gt   t1 colorid IN  SELECT colorid FROM colortable WHERE colorvalue    blue        - gt  AND     - gt   t2 colorid IN  SELECT colorid FROM colortable WHERE colorvalue    yellow      --------    dateid    --------       101    --------  1 row in set  0 00 sec    Hope  this would help many one

User · Answer

I think the problem is in your JOIN condition   SELECT user user fname         user user lname         parent user fname         parent user lname FROM users AS user JOIN users AS parent    ON parent user id   user user parent id WHERE user user id     GET id    Edit  You should probably use LEFT JOIN if there are users with no parents

User · Answer

I don t know how the table is created but try this     SELECT users1 user id  users2 user parent id FROM users AS users1 INNER JOIN users AS users2 ON users1 id   users2 id WHERE users1 user id   users2 user parent id

User · Answer

Your query should work fine  but you have to use the alias parent to show the values of the parent table like this   select    CONCAT user user fname       user user lname  AS  User Name     CONCAT parent user fname       parent user lname  AS  Parent Name  from users as user inner join users as parent on parent user parent id   user user id where user user id     GET id

User · Answer

Perhaps this should be the select  if I understand the question correctly   select user user fname  user user lname  parent user fname  parent user lname     As before

[mysql] INNER JOIN same table

Examples related to mysql

Examples related to sql

Examples related to join

Examples related to self-join