Parent - Child relationship within a single table

Question

I have a table which is like the following.

parentid   uid
10001      10001
10001      10002
10001      10003
10002      10004
10004      10005
10003      10006
10005      10007

I need to establish the parent child relationship between rows in this single table.

I need to get the parent in the reverse order till 4 levels. For example the last record is uid 10007 whose parentid is 10005. Now uid 10005's parent is 10004 and 10004's parent is 10002 and 10002's parent is 10001.

I am using MySQL so recursion seems to be not possible. What are the options that I have and how do I address this multi-level issue. I use PHP/MySQL.

Thanks in advance guys.

Answer 1

Since you have a finite 4 levels, you shouldn't need recursion (although it'd be handy to be able to use eg MS SQL CTEs).

Something like:

SELECT
  t4.uid as child, 
  --t3.uid as parent,
  --t2.uid as grand_parent,
  --t1.uid as great_grand_parent,
  t1.parentid as great_great_grand_parent
FROM
  your_table_name t1

  inner join your_table_name t2
  on t2.parentid = t1.uid

  inner join your_table_name t3
  on t3.parentid = t2.uid

  inner join your_table_name t4
  on t4.parentid = t3.uin

where 
  t4.uid = '10007' -- your start node.

If you need to do this for multiple nodes, you'd need to join this to something that selects your start nodes, or eg replace the above WHERE t4.uid = '10007' clause to be WHERE t4.uid IN (SELECT DISTINCT uid FROM your_table_name)

This was done freehand so apologies for typos.