Mysql hierarchy - check if node is parent

Question

I have a users table in this format:

|   ID   |   PARENT  |
|    1   |           |
|    2   |     1     |
|    3   |     1     | 
|    4   |     3     |
|    5   |     3     |
|    6   |     5     |

I wanted to create a query to verify if X is parent of Y. The examples i've found didn't help much as they don't work very well in multilevel hierarchy. Thanks for the help!

EDIT : the output can be the list of all parent of a node and in PHP I can check if the id is in the list.

I have tried this query:

select  id,
        parent_id 
from    (select * from users
         order by parent_id, id) users_sorted,
        (select @pv := '200911') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id);

But it only works for ids that are superior to the one we are searching for. I have a special case where the parent id is superior to his child's

Can you let us know what you want by way of sample input and output? — Tim Biegeleisen, Feb 24 '17 at 10:50
sure, i have edited the question. The query can retrieve the full list of parents of a node for instance — Diogo Mendonça, Feb 24 '17 at 10:52
Google for recursive hierarchical queries in MySQL. This question is a duplicate but I won't mark it as such. — Tim Biegeleisen, Feb 24 '17 at 10:53
For performing this kind of query efficiently, the phrase you need is "modified preorder traversal" - that should help you find plenty of articles on the table structure you need. — Paul Dixon, Feb 24 '17 at 11:10
See http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — Paul Dixon, Feb 24 '17 at 11:11
@PaulDixon, using that approach I would have to update the whole table every time I insert a new node? — Diogo Mendonça, Feb 24 '17 at 11:13
Yes - but an insertion typically involves just one extra query to increment the right value in affected rows. — Paul Dixon, Feb 24 '17 at 14:05
i was trying to avoid that from the start, but you are right. I'm going to give it a go — Diogo Mendonça, Feb 24 '17 at 15:16

Mysql hierarchy - check if node is parent

0 Answers0