I am able to generate a recursion query thanks to this post here: SQL Server recursion with multiple tables

But now I’m trying to get what a full listing of all the parent/child relationships in for multiple levels. Here is the example:

create table groups_groups (
[group_id] int not null,
[child_id] int not null
)

create table groups(group_id int not null)

insert into  groups_groups values (78,80)
insert into  groups_groups values (80,79)
insert into groups values (78)
insert into groups values (70)
insert into groups values (80)
;

with cte as (
select 0 as gglevel, 0 as parentid, groups.group_id , convert(varchar(max), concat(',', 0,':',groups.group_id)) as visited from groups
union all
select gglevel+1 as gglevel,  gg.group_id as parentid, gg.child_id,convert(varchar(max), concat(cte.visited,',',gg.group_id,':',gg.child_id)) as visited
from cte
join groups_groups gg on gg.group_id = cte.group_id
where visited not like concat('%,',gg.group_id,':',gg.child_id, ',%')
)
select * from cte
order by group_id,gglevel

fiddle

In the above, the visited column is there because in my full dataset, we have many loops and places where the parent equals the child and without, would have infinite loops.

This mostly works, except in the 4th row we see parent to group of 80-79 again. What I would hope to see is one row where 80 gives 79 (correct) and the level 2 row should be 78 gives 79 (it does because of 80 in the middle).

Is there a way to have levels 2 (and higher since we can have up to 10-20 levels) show all the possible combinations, all of the ways that I could be granted 79?

And is there a better way to prevent the infinite loop than this string concat/where clause?

Thank you!