user1718343 user1718343 - 1 month ago 9
MySQL Question

inner join same table

I am trying to get some rows from the same table, its a user table, user has user_id and user_parent_id I need to get the user_id row and user_parent_id row. so 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 not show the results. I want to display user record and its parent record.
any help would be appreciated.

regards

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.

Comments