mysql find top parent by given child id

To find the top parent by a given child ID in MySQL, you can use a recursive query. Here's an example of how you can achieve this:

WITH RECURSIVE cte AS (
  SELECT id, parent_id
  FROM your_table
  WHERE id = <child_id>
  UNION ALL
  SELECT t.id, t.parent_id
  FROM your_table t
  JOIN cte ON t.id = cte.parent_id
)
SELECT id, parent_id
FROM cte
WHERE parent_id IS NULL;

Replace <child_id> with the actual ID of the child you want to find the top parent for. This query uses a common table expression (CTE) with the WITH RECURSIVE syntax to recursively traverse the parent-child relationship until it reaches the top parent. The final SELECT statement retrieves the ID and parent ID of the top parent.

Note that you need to replace your_table with the actual name of your table.