0

I have a MySQL table with the structure below (around 1 million rows). Let's say I have two id's (X and Y), how could I check efficiently if X is a child (any level) of Y, or vice versa? My MySQL server version is 5.7.

Pseudo-code

IF X is child of Y:
    SELECT hierarchy from Y to X
ELSE IF Y is child of X:
    SELECT hierarchy from X to Y
ELSE:
    SELECT nothing

Database structure

parent_id | child_id | child_level
----------------------------------
1         | 5        | 1
1         | 6        | 1
1         | 2        | 1
2         | 7        | 2
2         | 8        | 2
2         | 9        | 2
2         | 4        | 2
2         | 3        | 2
3         | 10       | 3
3         | 11       | 3
4         | 12       | 3
4         | 13       | 3
4         | 14       | 3
konichiwa
  • 532
  • 1
  • 5
  • 23
  • Does the answer here answer your question? https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Rob Streeting May 25 '20 at 20:49
  • I wasn't sure how to use that answer with two inputs. That example uses a single input and takes all child. Is there no way to do this with two inputs? I think that would lead to two queries with their approach: X and Y. Not? – konichiwa May 25 '20 at 21:00

1 Answers1

0

You could use a recusive query - available in MySQL 8.0 only:

with recursive cte as (
    select root_id parent_id, child_id from mytablbe where parent_id = :X
    union all
    select c.root_id, t.child_id 
    from cte c
    inner join mytable t on t.parent_id = c.child_id
)
select * from cte where child_id = :Y

This query checks if :X is a parent of :Y: if yes, then one row is returned, else no row is returned.

GMB
  • 216,147
  • 25
  • 84
  • 135