SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tab(id INT, city VARCHAR(10), name VARCHAR(10))

INSERT INTO tab
          SELECT 1, 'Montreal', 'John'
UNION ALL SELECT 2, 'Montreal', 'Mary'
UNION ALL SELECT 3, 'Berlin', 'Mary'
UNION ALL SELECT 4, 'Berlin', 'Steve'
UNION ALL SELECT 5, 'Berlin', 'John'
UNION ALL SELECT 6, 'Tokyo', 'Steve'
UNION ALL SELECT 7, 'Tokyo', 'John'
UNION ALL SELECT 8, 'Moscow', 'Mary'
UNION ALL SELECT 9, 'Moscow','Steve'
9 rows affected

SELECT *
FROM tab


id
city
name


1
Montreal
John

2
Montreal
Mary

3
Berlin
Mary

4
Berlin
Steve

5
Berlin
John

6
Tokyo
Steve

7
Tokyo
John

8
Moscow
Mary

9
Moscow
Steve



WITH cte AS (
  SELECT city, STRING_AGG(name, ',') WITHIN GROUP(ORDER BY name)AS people
  FROM tab
  GROUP BY city
)
SELECT *
FROM tab t
WHERE EXISTS (SELECT 1
              FROM cte c
              WHERE t.city = c.city 
                AND CASE c.people
                       WHEN 'John,Mary' THEN 'Mary'
                       WHEN 'John,Mary,Steve' THEN 'John'
                       WHEN 'John,Steve' THEN 'Steve'
                       ELSE t.name
                    END = t.name)


id
city
name


2
Montreal
Mary

5
Berlin
John

6
Tokyo
Steve

8
Moscow
Mary

9
Moscow
Steve

id	city	name
1	Montreal	John
2	Montreal	Mary
3	Berlin	Mary
4	Berlin	Steve
5	Berlin	John
6	Tokyo	Steve
7	Tokyo	John
8	Moscow	Mary
9	Moscow	Steve