Recursive SQL query with multiple columns
<p>I have a table with the following columns</p>

<pre><code>idRelationshipType int,  
idPerson1 int,  
idPerson2 int
</code></pre>

<p>This table allows me to indicate records in a database that should be linked together.</p>

<p>I need to do a query returning all the unique ids where a person's id exists in idPerson1 or idPerson2 columns. Additionally, I need the query to be recursive so that the if I a match is found in idPerson1, the value for idPerson2 is included in the result set and used to repeat the query recursively until no more matches are found. </p>

<p>Example data:</p>

<pre><code>CREATE TABLE [dbo].[tbRelationships]
(
    [idRelationshipType] [int],
    [idPerson1] [int] ,
    [idPerson2] [int]
)

INSERT INTO tbRelationships (idRelationshipType, idPerson1, idPerson2) 
VALUES (1, 1, 2)  
INSERT INTO tbRelationships (idRelationshipType, idPerson1, idPerson2) 
VALUES (1, 2, 3)  
INSERT INTO tbRelationships (idRelationshipType, idPerson1, idPerson2) 
VALUES (1, 3, 4)  
INSERT INTO tbRelationships (idRelationshipType, idPerson1, idPerson2) 
VALUES (1, 5, 1)
</code></pre>

<p>Four 'Relationships' are defined here. For this query, I will only know one of the ids to begin with. I need a query that in concept works like</p>

<pre><code>SELECT idPerson 
FROM [some query] 
WHERE [the id i have to start with] = @idPerson 
  AND idRelationshipType = @idRelationshipType
</code></pre>

<p>The returned result should be a 5 rows with one column 'idPerson', with 1, 2, 3, 4, and 5 as the row values.</p>

<p>I have tried various combinations of UNPIVOT and recursive CTEs but I am not making much progress.</p>

<p>Any help would be greatly appreciated.</p>

<p>Thanks,
Daniel</p>