How to get all parents and ancestors from a MySql table?

Question

I'm using MySql and PHP and I have this table, where each item can contains other items and so on...

MyTable

RowId | ItemId | ChildItemId
1     | 1      | NULL
2     | 2      | NULL
3     | 3      | 1
4     | 4      | 1
5     | 4      | 2
6     | 5      | 3
7     | 5      | 4

Challenge: getting all parents

I would like a query that get all the parents/ancestors, at any hierarchy level, from a given ChildItemId.

Result expected

If I supply ChildItemId = 1

AllParents
3
4
5

Any help with query, loop, CTE, php code or whatever solution?

Answer 1

In the CTE, you can get all the parents/ancestors by generating all routing table using the recursive call. The following query filters by TargetItemId after generating table.

with recursive Ancesters as (
  select 1 as Level, ChildItemId as TargetItemId, RowId, ItemId as AncesterId, ChildItemId
  from MyTable
  where ChildItemId is not null
  union all
  select a.Level+1, a.TargetItemId, m.RowId, m.ItemId, m.ChildItemId
  from MyTable m inner join Ancesters a
  on m.ChildItemId = a.AncesterId
)
select distinct AncesterId from Ancesters where TargetItemId=1

You can also filter by ChildItemId in advance .

with recursive Ancesters as (
  select 1 as Level, ChildItemId as TargetItemId, RowId, ItemId as AncesterId, ChildItemId
  from MyTable
  where ChildItemId=1
  :