SQL Server Recursive Query for multiple rows on each recursion level

Question

I am trying to write a query to return all People that are 'Family' with Pat. I am not very familiar with loops in SQL Server but have read a little on them and am not sure how to structure the loop when I have multiple rows that I need to recursively loop in till no results are found then move to next row. Here is the data set I am working with.

Acquaintance table:

AcquaintanceID    Type
------------------------------
1                 Family
2                 Friend
3                 Colleague

People table:

PersonID    Name
---------------------------
1           Pat
2           Michael
3           Sarah
4           Barry
5           David
6           Chloe
7           Margaret
8           Jack
9           Jennifer
10          Daniel
11          Mary

Relations table:

RelationID  Person1ID   Person2ID   AcquaintanceID
---------------------------------------------------
1           1           3           1
2           1           2           1
3           1           4           2
4           2           5           2
5           2           8           3
6           2           6           1
7           3           6           3
8           3           9           2
9           3           4           3
10          4           7           3
11          4           10          3
12          4           11          2

The query I am running to get the first step is

SELECT 
    Relations.Person1ID, P1.Name, Relations.Person2ID, P2.Name, 
    Relations.AcquaintanceID, Acquaintance.Type
FROM
    Relations 
INNER JOIN 
    People P1 ON Relations.Person1ID = P1.PersonID
INNER JOIN 
    People P2 ON Relations.Person2ID = P2.PersonID
INNER JOIN 
    Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
WHERE 
    P1.Name = 'Pat' AND Acquaintance.Type = 'Family'

This returns the IDs from Person2ID that I need to then run the same query on but for that PersonID...

I am trying to get a list of all the People that are Family / Extended Family with Pat when all is said and done.

Like

Name
----------
Sarah
Michael
Chloe

I just managed to solve this on my own with the use of a Union and a SubQuery but am still curious to how this would be set up with Recursion. Query is below.

SELECT
    P2.Name AS Name
FROM 
    Relations 
INNER JOIN 
    People P1 ON Relations.Person1ID = P1.PersonID
INNER JOIN 
    People P2 ON Relations.Person2ID = P2.PersonID
INNER JOIN 
    Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
WHERE 
    P1.Name = 'Pat' AND Acquaintance.Type = 'Family'
UNION ALL
SELECT
    P2.Name
FROM 
    Relations 
INNER JOIN 
    People P1 ON Relations.Person1ID = P1.PersonID
INNER JOIN 
    People P2 ON Relations.Person2ID = P2.PersonID
INNER JOIN 
    Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
INNER JOIN (
    SELECT
        Relations.Person2ID, P2.Name AS P2Name, Acquaintance.Type
    FROM 
        Relations 
    INNER JOIN 
        People P1 ON Relations.Person1ID = P1.PersonID
    INNER JOIN 
        People P2 ON Relations.Person2ID = P2.PersonID
    INNER JOIN 
        Acquaintance ON Relations.AcquaintanceID = Acquaintance.AcquaintanceID
    WHERE 
        P1.Name = 'Pat' AND Acquaintance.Type = 'Family'
    ) A ON Relations.Person1ID = A.Person2ID
WHERE Acquaintance.Type = 'Family'

Output:

Name
----------
Sarah
Michael
Chloe

Great job on providing sample tables and query. A table with desired outputs would be appreciated. — Simon
– Simon, Commented Oct 3, 2017 at 15:59
You should be able to do this with a recursive CTE. There are plenty of examples on here — SQLBadPanda
– SQLBadPanda, Commented Oct 3, 2017 at 16:52

Eric · Accepted Answer · 2017-10-03 17:54:25Z

2

You can use recursive CTE. You will 2 parts: the anchor, and the recursive part.

WITH cte (Lvl, Person1ID, Person1Name, Person2ID, Person2Name)
AS (
    -- Anchor part: Start with 'Pat'
    SELECT 0 AS Lvl, p.PersonID, p.Name, (SELECT PersonID FROM People WHERE Name = 'Pat'), CAST('' AS VARCHAR(50))
    FROM Relations r
    JOIN People p ON p.PersonID = r.Person1ID
    WHERE p.PersonID = (SELECT PersonID FROM People WHERE Name = 'Pat')
    UNION ALL
    -- Recursive part: 
    SELECT Lvl + 1, p.PersonID, p.Name, a.PersonID, CAST(a.Name AS VARCHAR(50))
    FROM Relations r
    JOIN People p ON p.PersonID = r.Person1ID
    JOIN People a ON a.PersonID = r.Person2ID
    JOIN cte c ON c.Person2ID = r.Person1ID
    WHERE r.AcquaintanceID = (SELECT AcquaintanceId FROM Acquaintance WHERE Type = 'Family')
)
SELECT DISTINCT * FROM cte

answered Oct 3, 2017 at 17:54

Eric

3,2701 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLBadPanda Over a year ago

That's exactly what I meant :)

Simon · Accepted Answer · 2017-10-03 17:48:59Z

1

There are many ways to do this using a recursive cte, here is one of many:

;with cte1
as
(select p.Name, r.Person2ID, r.AcquaintanceID
from Relations r
inner join People p on p.PersonID in (r.Person1ID, r.Person2ID)
inner join Acquaintance a on a.AcquaintanceID = r.AcquaintanceID
where r.Person1ID = 1 and r.AcquaintanceID = 1),

cte2
as
(select r.Person2ID
from Relations r
inner join cte1 on cte1.AcquaintanceID = r.AcquaintanceID)

select p.Name
from cte2
left join People p on p.PersonID = cte2.Person2ID
group by p.Name

I made a rextester example you can play with here

answered Oct 3, 2017 at 17:48

Simon

1,2019 silver badges19 bronze badges

Collectives™ on Stack Overflow

SQL Server Recursive Query for multiple rows on each recursion level

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related