Select keys connected by its values

Question

I have two columns something like keys and values. I need group keys regarding connections between values like is shown in picture. The same values connects keys. I have no idea how to do it. Using 10.5.8-MariaDB.

Answer 1

This is a typical graph-walking problem - which requires a recursive query, available starting MariaDB 10.2.2.

Here is an approach that starts by building edges as tuples of connected nodes, and then iteratively walks the dataset, keeping track of already visite nodes. We can the identify which group each node belongs to by tracking the minimum visited node.

with recursive 
    edges as (
        select t1.lagr_number as lagr_number1, t2.lagr_number as lagr_number2
        from mytable t1
        inner join mytable t2 on t2.ltran_number = t1.ltran_number
    ),
    cte as (
        select lagr_number1, lagr_number2, concat(lagr_number1, ',', lagr_number2) as visited
        from edges
        union all
        select c.lagr_number1, e.lagr_number2, concat(c.visited, ',', e.lagr_number2)
        from cte c
        inner join edges e on e.lagr_number1 = c.lagr_number2
        where not find_in_set(e.lagr_number2, c.visited)
    )
select lagr_number1 as lagr_number, dense_rank() over(order by min(lagr_number2)) as grp
from cte
group by lagr_number1
order by grp, lagr_number1

Demo on DB Fiddle

Sample data:

lagr_number | ltran_number
:---------- | :-----------
K000001     | V000001     
K000001     | V000004     
K000001     | V000005     
K000002     | V000001     
K000003     | V000002     
K000003     | V000003     
K000004     | V000005     
K000005     | V000007     
K000005     | V000008     
K000006     | V000009     
K000007     | V000009     

Results:

lagr_number | grp
:---------- | --:
K000001     |   1
K000002     |   1
K000004     |   1
K000003     |   2
K000005     |   3
K000006     |   4
K000007     |   4