cte test, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

create table Acquaintance(AcquaintanceID int, Type varchar(15))
create table People(PersonID int, Name varchar(15))
create table Relations(RelationID int, Person1ID int, Person2ID int, AcquaintanceID int)

insert into Acquaintance values(1, 'Family')
                               ,(2, 'Friend')
                               ,(3, 'Colleague')
                                
insert into People values(1, 'Pat')
                         ,(2, 'Michael')
                         ,(3, 'Sarah')
                         ,(4, 'Barry')
                         ,(5, 'David')
                         ,(6, 'Chloe')
                         ,(7, 'Margaret')
                         ,(8, 'Jack')
                         ,(9, 'Jennifer')
                         ,(10, 'Daniel')
                         ,(11, 'Mary')
insert into Relations values(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)
;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

View schema
Execution time: 0,03 sec, rows selected: 3, rows affected: 26, absolute service time: 0,19 sec