This feels like it should be easy but I just can't quite nail it.

I'm working on a system that logs "Persons".
One Person can be "Merged" into another to indicate that they're actually the same person.
In order to support Unmerging, rather than physically merging the two records and repointing all their child records in other tables, instead we hold a "Merge" record which says e.g. Person A has Been Merged into Person B. From an end users point of view Person A then ceases to exist (only people who haven't been merged into someone else appear in the UI) although, in data terms, Person A's record does still exist and has all it's children attached to it - it's just that the whole caboodle will henceforth be considered part of Person B.

A couple of extra rules:-
A person can only ever be merged into 1 other person.
That person could then be merged into a third and so on. Forming a "Chain"
Many persons can be merged into 1 person.

Hope that all makes sense

So here's some data that would produce a single "merged" person (ID 6) out of several original persons:-
Code:
Insert into MergedPersons
(MergedPersonID, MergedIntoPersoniD)
Values (2,3),
(3,4),
(4,5),
(5,6),
(7,6),
(8,7),
(9,7);
I'm now looking for an efficient way, given a single merge pairing, to get every person id in the chain. My first thought was simply to use a recursive cte. However, because I might start with a pairing in the middle of a chain I need to recurse in both directions along it. I therefore wrote this:-
Code:
with cteMergeChain as
(
      Select [Merged Person ID], [Merged Into Person ID]
      From People.[Merged Persons]
      Where [Merged Person ID] in (@PersonID1, @PersonID2)
      and [Merged Into Person ID] in (@PersonID1, @PersonID2)
      
      Union All
      
      (Select MP.[Merged Person ID], MP.[Merged Into Person ID]
      From cteMergeChain MC
      Join People.[Merged Persons] MP
           on MC.[Merged Person ID] = MP.[Merged Into Person ID]
           or MC.[Merged Into Person ID] = MP.[Merged Person ID])
)
Select [Merged Person ID]
From cteMergeChain

Union

Select [Merged Into Person ID]
From cteMergeChain
...but that creates an infinite recursion.

Say I start with pairing (4,5). My first iteration will get me additional pairings:-
(3,4) and (5,6). OK so far.
But the next iteration, as well as finding (2,3) and (7,6), will return me (4,5) again. Thus begins an infinite recursion.

My first thought was simply to add an Except to the recursing part of the cte:-
Code:
with cteMergeChain as
(
      Select [Merged Person ID], [Merged Into Person ID]
      From People.[Merged Persons]
      Where [Merged Person ID] in (@PersonID1, @PersonID2)
      and [Merged Into Person ID] in (@PersonID1, @PersonID2)
      
      Union All
      
      (Select MP.[Merged Person ID], MP.[Merged Into Person ID]
      From cteMergeChain MC
      Join People.[Merged Persons] MP
           on MC.[Merged Person ID] = MP.[Merged Into Person ID]
           or MC.[Merged Into Person ID] = MP.[Merged Person ID]
      Except 
      Select [Merged Person ID], [Merged Into Person ID]
      From cteMergeChain)
)
Select [Merged Person ID]
From cteMergeChain

Union

Select [Merged Into Person ID]
From cteMergeChain
However that's not allowed because "Recursive member of a common table expression 'cteMergeChain' has multiple recursive references". Which is SQL Server speak for "you can't refer back to the cte a second time".

At the moment I've fallen back on recursing all the way along to the end of the chain in one direction, getting the ID at the end of the chain and then using that as the start point to recurse all the way back to the other end, collecting all the IDs as I go. This seems a bit wasteful though.

I've cut this a few ways but I just can't seem to come up with a solution that recurses in both directions at the same time without causing an infinite recursion.

Anyone got any suggestions?