Select users with the same cities

Question

I have an user(id, name), city(id, name) and a join table users_cities(user_id, city_id) for a many to many relationship, like:

cities

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

users

 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

There is a policy for the index of users where the current user can only see the list of users that doesn't have a city he doesn't have. Also:

An user with some cities can see a user with no cities. And an user with no cities can see a user with no cities.

For a given user id, how can I select all users that have the same cities or a subset of it?

For example when user James (with cities 1 and 3) is logged in, I expect him to see:

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

Discarding user Robert because Robert has city 4 and James doesn't. Or equivalently, Robert's cities (1,4) are not a subset of James's (1,3)

EDIT: This sql shows the correct output for the example data and user 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

And shows the correct ouput:

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

But I don't know how to generate all the combinations of 1,3 ('1,3', '3,1', '1', '3') to use in the having clause for a given user id

This question it's very different than SELECTING with multiple WHERE conditions on same column because the condition for filtering users it is not in the same column or table, it is in the many-to-many relation.

Would a user with some cities see a user with no cities? Would a user with no cities see a user with no cities? — Strawberry, Oct 18 '18 at 18:09
@Strawberry Regarding your questions, I say yes to both. Why did you mark the question as duplicate? — Guillermo, Oct 18 '18 at 18:42

DanB · Answer 1 · 2018-10-19T13:42:38.897

I would do :

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>;

Here the tricks to do this :

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)

I think you meant: `SELECT users.* FROM users INNER JOIN users_cities ON users_cities.user_id = users.id WHERE users_cities.city_id IN (select city_id from users_cities where users_cities.user_id = 1)` But this doesn't meet the requirements, users are duplicated and id shouldn't show user Robert. — Guillermo, Oct 18 '18 at 22:14

Select users with the same cities

1 Answers1