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.
You can view self-join as two identical tables. But in normalization, you cannot create two copies of the table so you just simulate having two tables with self-join. Suppose you have two tables: Table emp1 Id Name Boss_id 1 ABC 3 2 DEF 1 3 XYZ 2 Table emp2 Id Name Boss_id 1 ABC 3 … Read more
WITH q AS ( SELECT * FROM mytable WHERE ParentID IS NULL — this condition defines the ultimate ancestors in your chain, change it as appropriate UNION ALL SELECT m.* FROM mytable m JOIN q ON m.parentID = q.PersonID ) SELECT * FROM q By adding the ordering condition, you can preserve the tree order: … Read more
You use a self join when a table references data in itself. E.g., an Employee table may have a SupervisorID column that points to the employee that is the boss of the current employee. To query the data and get information for both people in one row, you could self join like this: select e1.EmployeeID, … Read more