How to get the relational sub-components using query?

Question

I have a table with parent-child in a same table.

First I run query SELECT id, pid FROM table_relation where pid = 10. This gives result

| id  | pid  |
| --- | ---- |
| 92  | 10   |
| 97  | 10   |
| 100 | 10   |

From the result I have make query with id = 92; 97 & 100 And again make another query with the id result.

I can achieve this result by using loop But this way I have make too many loops. Is there a way to acheive the result only using query ?

I tried using UNION but it doesn't give me correct result.

SELECT
    e.id,
    e.pid
FROM
    table_relation AS e
WHERE
    e.pid IN (select id from table_relation where pid = 10)
order by e.id ) UNION
(SELECT
    e.id,
    e.pid
FROM
    table_relation AS e
WHERE
    e.id IN (select id from table_relation where pid = 10 order by id)) order by id

Answer 1

MySQL version 8+, there is recursive common table expression

WITH RECURSIVE cte AS (
  SELECT id, pid
  FROM table_relation
  WHERE pid = 10
  UNION
  SELECT table_relation.id, table_relation.pid
  FROM table_relation
  JOIN cte ON cte.id = table_relation.pid
)
SELECT *
FROM cte