Recursive query in mysql_

Question

I have a hierarchy of organizations in organization_table. In this table I've data like,

--------------------------------------------------------------
organization_id | organisation_type | organization_parent_id |    
--------------------------------------------------------------  Sample
101             |   Primary         | 101                    |   101
102             |   Secondary       | 101                    |    |
103             |   Secondary       | 102                    |   102
104             |   Primary         | 104                    |    |
105             |   Secondary       | 104                    |   103
106             |   Secondary       | 105                    |

Here 101 is a primary parent for 101,102 and 103 same as 104 is primary parent for 104, 105 and 106. So by query I need to find Primary parent for record_id 103 and 106.

I'm weak at recursive query execution so please help me out to a suitable way with this.

Thanks in Advance.!

Answer 1

If I understand correctly, the recursive CTE looks like:

with recursive cte as (
      select organization_id, organization_parent_id, 1 as lev
      from organization_table ot
      where organization_id in (103, 106)
      union all
      select cte.organization_id, ot.organization_parent_id, lev + 1
      from cte join
           organization_table ot
           on ot.organization_id = cte.organization_parent_id
      where organization_id in (103, 106)
    )
select cte.*
from (select cte.*,
             row_number() over (partition by organization_id order by lev desc) as seqnum
      from cte
     ) cte
where seqnum = 1;

Answer 2

WITH RECURSIVE
cte AS ( SELECT organization_id, organization_parent_id
         FROM organization_table
         WHERE organization_id IN (103, 106)
       UNION ALL
         SELECT ot.organization_id, ot.organization_parent_id
         FROM cte
         JOIN organization_table ot ON ot.organization_id = cte.organization_parent_id
         WHERE cte.organization_id != cte.organization_parent_id
       )
SELECT *
FROM cte
WHERE organization_id = organization_parent_id;

fiddle