mysql, find parent node

Question

I have a standard hierarchical table ID/PID and I need to find (boolean) if some child belongs to some ID. Is there a way to do this within mysql?

ID   PID
------------
1     0          <- root
...   ...
...   ... 
...   ...
7     1
...   ...
16    1
...   ...
4     0
...   ...
22    16
25    16

Is there a way to know if child node 22 belongs to node 1 ? I'm asking because I want to implement some Delete procedure, where node 1 is my Recycle. All deletes belongs to 1 will be deleted permanently and all other delete will have PID replaced, so temporarily belongs now to 1.

Thanks very much in advance,

You should take a look at stackoverflow.com/a/3276806/477878, this is not entirely straight forward using MySQL. — Joachim Isaksson
– Joachim Isaksson, Commented Feb 2, 2013 at 11:46
if you want to do this with a single query, use the nested set model — Karoly Horvath
– Karoly Horvath, Commented Feb 2, 2013 at 12:00
I think NULL better reflects the idea that not only is a root's parent unknown but also it may not necessarily be shared with another root. BTW, in this instance, I would definitely look at nested sets. — Strawberry
– Strawberry, Commented Feb 2, 2013 at 12:00

Community · Accepted Answer · 2017-05-23 12:18:57Z

1

Based on Hierarchical queries in MySQL :

SET @x := 22;
SET @y := 0;

select 'yes' as x_comes_from_y 
from dual
where @y in
(
 SELECT  @id :=
        (
        SELECT  pid
        FROM    h
        WHERE   id = @id
        ) AS nodes
 FROM    (
        SELECT  @id := @x
        ) vars
 STRAIGHT_JOIN
        h
 WHERE   @id IS NOT NULL
);

Test it.

edited May 23, 2017 at 12:18

CommunityBot

11 silver badge

answered Feb 2, 2013 at 12:08

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1973900 Over a year ago

Wow, this is absolutely excellent :) You give me homework to understand how it works! Thanks very much sir!

dani herrera Over a year ago

Remember that this is not a hight performance approach. They are other techniques to do it in real work environment: stackoverflow.com/questions/8961789/genealogy-tree-algorithm

user1973900 · Accepted Answer · 2013-02-02 22:53:04Z

0

Once again, thanks for this ellegant solution. I simplified a little since I don't need STRAIGHT_JOIN

SET @x := 52;
SET @y := 41;

select 'yes' as x_comes_from_y 
from dual
where @y in
(
 SELECT  @id :=
        (
        SELECT  pid
        FROM    cat
        WHERE   id = @id
        ) 
 FROM    
        cat
 WHERE   @id IS NOT NULL
);

PS. There is a little drawback when use to get objects full path, result always returning last row NULL but this can be avoided with another SELECT.

answered Feb 2, 2013 at 22:53

user1973900

3814 silver badges20 bronze badges

Collectives™ on Stack Overflow

mysql, find parent node

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related