SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE EmployeeLocationAssn (
[EmployeeLocationAssnId] [int]  IDENTITY(1,1) NOT NULL, 
[EmployeeId] [int] NOT NULL,
[LocationId] [int] NOT NULL
)
--This table contains data for employees and their associated locations.

INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (1, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (1, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (2, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (2, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (3, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (3, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 4)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (5, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (5, 4)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 4)
16 rows affected

declare @EmployeeId int = 1;

with cte as (
    select E.EmployeeId
        , string_agg(E.LocationId,',') within group (order by LocationId asc) LocationGroup
    from EmployeeLocationAssn E
    group by E.EmployeeId
)
select EmployeeId
from cte
where LocationGroup = (select LocationGroup from cte where EmployeeId = @EmployeeId);


EmployeeId


1

2

3


declare @EmployeeId int = 1;

with cte as (
  select E.EmployeeId, 
    substring(
      (
        select ',' + convert(varchar(12),E1.LocationId) as [text()]
        from EmployeeLocationAssn E1
        where E1.EmployeeId = E.EmployeeId
        order by E1.LocationId
        for xml path ('')
      ), 2, 1000) LocationGroup
  from EmployeeLocationAssn E
  group by E.EmployeeId
)
select EmployeeId
from cte
where LocationGroup = (select LocationGroup from cte where EmployeeId = @EmployeeId);


EmployeeId


1

2

3