mysql 获取 父节点,mysql，找到父节点

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

... ...

... ...

... ...

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,

解决方案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

);