Get all parent nodes

Question

Without using stored procedures, is there a way to get all parent nodes as individual rows?

Example data:

id | parent_id | name
---------------------
 1 |      NULL | Cat1
 2 |      NULL | Cat2
 3 |         1 | Cat3
 4 |         3 | Cat4
 5 |         4 | Cat5
 6 |         2 | Cat6
 7 |      NULL | Cat7

When getting all parents for row where id = 5, results would be:

id | parent_id | name
---------------------
 1 |      NULL | Cat1
 3 |         1 | Cat3
 4 |         3 | Cat4

I should note: I'm ok with doing multiple joins (thereby creating a fixed limit to the number of parents retrieved).

possible duplicate of [Connect By Prior Equivalent for MySQL](http://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql) — sampathsris, Jul 15 '14 at 04:03
That question is asking about child nodes, not parents, and it does not specify each result being an individual row. — Ian, Jul 15 '14 at 04:07
It's the same thing. What you have is a recursive relation. What you have to do is start from the child (leaf) node and go bottom up instead of going top down. — sampathsris, Jul 15 '14 at 04:13

score 2 · Answer 1 · answered Sep 23 '22 at 03:17

For MySQL 5.5, 5.6 & 5.7

SELECT @r AS user_id, 
   (SELECT @r := parent_id FROM users_table WHERE id = user_id) AS parent_id, 
   @l := @l + 1 AS level 

   FROM (SELECT @r := 9, @l := 0) val, users_table WHERE @r <> 0

Note : @r := 9. Where 9 is the child user's id.

See fiddle here

The above query is deprecated in MySQL 8. So here is the query for MySQL 8.0

with recursive parent_users (id, parent_id, level) AS (
  SELECT id, parent_id, 1 level
  FROM users_table
  WHERE id = 9
  union all
  SELECT t.id, t.parent_id, level + 1
  FROM users_table t INNER JOIN parent_users pu
  ON t.id = pu.parent_id
)
SELECT * FROM parent_users;

Note : id = 9. Where 9 is the child user's id.

See fiddle here

score 1 · Answer 2 · answered Jul 15 '14 at 03:43

1

can you try this query.

select a.* 
  from table1 a 
 inner join  table1 b 
    on a.id = b.parent_id
 where b.id <= 5

Thanks.

Here is the Sql Fiddle

answered Jul 15 '14 at 03:43

noobdeveloper

420
3
14

1

User want `where b.id = 5` not `where b.id <= 5` – Sadikhasan Jul 15 '14 at 03:44

score 1 · Answer 3 · answered Aug 02 '17 at 13:43

Similar post with answer Finding all parents in mysql table with single query (Recursive Query)

I have parent id stored in different table

SELECT
  T2.id,
  T2.name
FROM (
       SELECT
         @r AS _id,
         (SELECT @r := parent_id FROM record_parents WHERE record_id = _id) AS parent_id,
         @l := @l + 1 AS lvl
       FROM
         (SELECT @r := 25797, @l := 0) vars,
         records m
       WHERE @r <> 1) T1
  JOIN records T2 ON T1._id = T2.id
ORDER BY T1.lvl DESC;

modified code from above post

score 0 · Answer 4 · answered Jul 15 '14 at 05:57

You can try to introduce an additional column - path where path to parent is represented as string <root id>_<first level child id>_<second level child id>_ etc

so the path for 5 would be "1_3_4". You would need a trigger to update the path as parent node path +"_<parent id>". So when you insert the cat5 sibling 8|4|cat8 path to parent would be the same"1_3_4".

Then to find all path to parent rows you can check whether path to parent contains the id.

Get all parent nodes

4 Answers4