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 http://stackoverflow.com/a/3276806/477878, this is not entirely straight forward using MySQL. — Joachim Isaksson, Feb 02 '13 at 11:46
if you want to do this with a single query, use the nested set model — Karoly Horvath, Feb 02 '13 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, Feb 02 '13 at 12:00

score 1 · Accepted Answer · edited May 23 '17 at 12:18

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 '17 at 12:18

Community

1
1

answered Feb 02 '13 at 12:08

dani herrera

48,760
8
117
177

Wow, this is absolutely excellent :) You give me homework to understand how it works! Thanks very much sir! – user1973900 Feb 02 '13 at 12:25
Remember that this is not a hight performance approach. They are other techniques to do it in real work environment: http://stackoverflow.com/questions/8961789/genealogy-tree-algorithm – dani herrera Feb 02 '13 at 13:16

score 0 · Answer 2 · answered Feb 02 '13 at 22:53

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.

mysql, find parent node

2 Answers2

Linked