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.
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.
