选择相同城市的用户

Question

对于多对多关系，我有一个用户(id，名称)，一个城市(id，名称)和一个连接表users_cities(user_id，city_id)，如下所示：

cities

 id | name 
----|------ 
  1 | NY   
  2 | LA   
  3 | CH   
  4 | HU

用户

 id | name 
----|------ 
  1 | James   
  2 | Michael  
  3 | Robert   
  4 | Maria

users_cities

 user_id | city_id 
---------|--------- 
       1 |       1 
       1 |       3 
       2 |       3 
       3 |       1 
       3 |       4 
       4 |       1 
       4 |       3

有一个用户索引的策略，当前用户只能看到没有城市的用户列表。另外：

具有某些城市的用户可以看到没有城市的用户。没有城市的用户可以看到没有城市的用户。

对于给定的用户id，如何选择具有相同城市或其子集的所有用户？

例如，当用户James (城市1和3)登录时，我希望他看到：

 id | name 
----|------ 
  1 | James   
  2 | Michael 
  4 | Maria

丢弃用户Robert，因为Robert有城市4，而James没有。

James EDIT：这个显示了示例数据和用户James的正确输出：

SELECT users.* 
FROM   users 
       INNER JOIN users_cities 
               ON users_cities.user_id = users.id 
       INNER JOIN cities 
               ON cities.id = users_cities.city_id 
GROUP  BY users.id 
HAVING Group_concat(cities.id) IN ('1,3', '3,1', '1', '3')

See this DB fiddle

并显示正确的输出：

 id | name 
----|------ 
  1 | James   
  2 | Michael
  4 | Maria

但是我不知道如何为给定的用户id生成在having子句中使用的1,3 ('1,3'，'3,1'，'1'，'3')的所有组合

这个问题与SELECTING with multiple WHERE conditions on same column非常不同，因为过滤用户的条件不在同一列或表中，而是在多对多关系中。

Answer 1

我会这样做：

SELECT users.* 
FROM   users 
       INNER JOIN users_cities ON users_cities.user_id = users.id 
WHERE  users_cities.id in (select id from users_cities.user_id = <JamesId>)
       and users.id <> <JamesId>;

下面是实现这一点的技巧：

select id, name
from (
SELECT users.*, users_cities.city_id, ifnull(james_cities.city_id, 'NULL') james_city_id
FROM users
INNER JOIN users_cities ON users_cities.user_id = users.id
left join users_cities james_cities on james_cities.city_id = users_cities.city_id and james_cities.user_id = 1
union
select users.*, 'NULL', 'NULL'
from users where id not in (select user_id from users_cities)
) a 
group by id, name
having group_concat(city_id) = group_concat(james_city_id)