SQL: Find intersection in a double many-to-many relatiion

Question

Following is a simplified version of my schema and data:

users:

id | name
 1 | Peter
 2 | Max
 3 | Susan

restaurants:

id | name
 1 | Mario
 2 | Ali
 3 | Alfonzo
 4 | BurgerQueen

dishes:

id | name
 1 | Burger
 2 | Pizza
 3 | Salad

users_dishes:

user_id | dish_id
      1 | 1
      2 | 1
      2 | 2
      3 | 2
      3 | 3

restaurants_dishes:

restaurant_id | dish_id
            1 | 2
            1 | 3
            2 | 1
            2 | 3
            3 | 1
            3 | 2
            3 | 3
            4 | 1

So I have three entities: users, restaurants and dishes. And two many-to-many relations.

The relation users-dishes defines what a user can eat.
The relation restaurants-dishes defines what a restaurant can serve.

As input I have a list of user IDs. What I need now, is to find all restaurants where all users from the list can eat what they like.

Consider the following query:

select u.name as user, group_concat(distinct r.name) as dishes
from users u
join users_dishes ud on ud.user_id = u.id
join restaurants_dishes rd on rd.dish_id = ud.dish_id
join restaurants r on r.id = rd.restaurant_id
group by u.id

This shows all restaurants, that each user can visit.

user  | restaurants
Peter | Alfonzo,Ali,BurgerQueen
Max   | Alfonzo,Ali,BurgerQueen,Mario
Susan | Alfonzo,Ali,Mario

So what I need is the intersection of the sets. You can already see that all three users can go to Alfonzo and Ali. But Peter can not go to Mariao. And Susan can not go to BurgerQueen.

The result (for the user IDs 1,2,3) should be:

id | name
 2 | Ali
 3 | Alfonzo

For IDs 1, 2 it should be

id | restaurant
 2 | Ali
 3 | Alfonzo
 4 | BurgerQueen

For IDs 2, 3 it should be

id | restaurant
 1 | Mario
 2 | Ali
 3 | Alfonzo

You can create schema and sample data with the following SQL script:

CREATE TABLE users (id INT AUTO_INCREMENT,name varchar(100),PRIMARY KEY (id));
INSERT INTO users(name) VALUES ('Peter'),('Max'),('Susan');
CREATE TABLE restaurants (id INT AUTO_INCREMENT,name varchar(100),PRIMARY KEY (id));
INSERT INTO restaurants(name) VALUES ('Mario'),('Ali'),('Alfonzo'),('BurgerQueen');
CREATE TABLE dishes (id INT AUTO_INCREMENT,name varchar(100),PRIMARY KEY (id));
INSERT INTO dishes(name) VALUES ('Burger'),('Pizza'),('Salad');
CREATE TABLE users_dishes (user_id INT,dish_id INT,PRIMARY KEY (user_id, dish_id),INDEX (dish_id, user_id));
INSERT INTO users_dishes(user_id, dish_id) VALUES (1,1),(2,1),(2,2),(3,2),(3,3);
CREATE TABLE restaurants_dishes (restaurant_id INT,dish_id INT,PRIMARY KEY (restaurant_id, dish_id),INDEX (dish_id, restaurant_id));
INSERT INTO restaurants_dishes(restaurant_id, dish_id) VALUES (1,2),(1,3),(2,1),(2,3),(3,1),(3,2),(3,3),(4,1);

I have also prepared an SQL-fiddle on db-fiddle.com.

I should also mention that I need a solution compatible to MySQL 5.7 and MariaDB 10.1

What happens when you have ties? I mean, if multiple disjoint subsets are tied in first place? — The Impaler, Mar 13 '19 at 18:42
@TheImpaler I'm not sure what you mean. I want all restaurants that match the condition. — Paul Pushkin, Mar 13 '19 at 18:48
*all users from the list can eat what they like* this means all the dishes they like or at least 1 dish that they like? — forpas, Mar 13 '19 at 18:51
The question is not well formulated. For example, if `Susan` & `Peter` went to `Ali` and `Mario`; but them `Max` & `Susan` went to `Alfonzo` and `Mario`. Which one wins? This is just a simple case. There can be many other ugly combinations. — The Impaler, Mar 13 '19 at 18:52
@TheImpaler In that case I should get an empty result. I'm open to suggestions, how to make the question more clear. — Paul Pushkin, Mar 13 '19 at 18:55
@TheImpaler: I found the question rather clear, and it's a very classic kind of SQL problem, called the relational division. The downvoting was a bit harsh IMO. — Lukas Eder, Mar 13 '19 at 19:04
@LukasEder OK, I think the problem is the example data shows a very simple case -- "the sunny path". If you only want to get the solution for this simple case, yes, all the answers are good. But the generic case is far more complex. I chose to downvote the question, instead of downvoting all the answers, since I think they are all wrong. No answer so far, is producing all combinations (in the math sense), to select the best ones (yes, plural). — The Impaler, Mar 13 '19 at 19:34
@TheImpaler: You're clearly overengineering this. And now, I'm expecting a perfect answer from you :) — Lukas Eder, Mar 13 '19 at 19:41
@TheImpaler I agree that my example with user IDs (1, 2, 3) was not the best, since it covers the full table. So I added two more examples with IDs (1, 2) and (2, 3). I know you've posted an answer with a CTE. But it disappeared before I could comment it. I guess the CTE could be replaced by a subquery. — Paul Pushkin, Mar 13 '19 at 19:58
@SalmanA I just was writing a comment on your answer and wanted to upvote it. But it also disappeared :-( I did a mistake in my test. Your answer also worked. — Paul Pushkin, Mar 13 '19 at 20:10
@TheImpaler seriously? All answers are based on Math, all based on this: https://www.red-gate.com/simple-talk/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ Now you go and tell that person that he's wrong "in the math sense". — Salman A, Mar 13 '19 at 20:33

Lukas Eder · Answer 1 · 2019-03-13T19:31:01.970

4

Classic relational division. One of the "easiest" approaches is this one:

select *
from restaurants r
where not exists (
  select *
  from users u
  where not exists (
    select *
    from users_dishes ud
    join restaurants_dishes rd on ud.dish_id = rd.dish_id
    where ud.user_id = u.id
    and rd.restaurant_id = r.id
  )
  and u.id in (1, 2, 3)
)

Demo here. In other words, if there were a user for which there was no dish in a given restaurant, then that given restaurant could not accommodate all users. So, we want to get the restaurants for which there is no user, for which there is no dish in that restaurant.

edited Mar 13 '19 at 19:31

answered Mar 13 '19 at 18:44

Lukas Eder

211,314
129
689
1,509

A double not-exists is not easy! – Salman A Mar 13 '19 at 19:26
@SalmanA: It is quite easy, but we humans are not programmed to think in terms of doubly negated boolean sets. – Lukas Eder Mar 13 '19 at 19:28
Thank you! That is working too in my tests. And I like that the user ID list is the only non static part of the query. But it looks quite complex with the double nested `not exists` subqueries. So I will go forpas' solution. But you've deserved my upvote. – Paul Pushkin Mar 13 '19 at 19:30
@PaulPushkin: Yep. Make sure to read up on the many other alternative implementations: https://www.red-gate.com/simple-talk/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/ – Lukas Eder Mar 13 '19 at 19:32
Will have a look at it. Tanks again. – Paul Pushkin Mar 13 '19 at 19:45

forpas · Accepted Answer · 2019-03-13T19:05:16.557

3

I modified your query to group by restaurant name and count the users that can eat at each one of them and added a condition with having:

select r.id, r.name as restaurant
from users u
join users_dishes ud on ud.user_id = u.id
join restaurants_dishes rd on rd.dish_id = ud.dish_id
join restaurants r on r.id = rd.restaurant_id
group by r.id, r.name
having count(distinct u.id) = (select count(*) from users);

Results:

| id  | restaurant |
| --- | ---------- |
| 2   | Ali        |
| 3   | Alfonzo    |

See the demo
You can add a condition to check for a list of users like this:

select r.id, r.name as restaurant
from users u
join users_dishes ud on ud.user_id = u.id
join restaurants_dishes rd on rd.dish_id = ud.dish_id
join restaurants r on r.id = rd.restaurant_id
where u.id in (1, 2, 3)
group by r.id, r.name
having count(distinct u.id) = 3;

edited Mar 13 '19 at 19:05

answered Mar 13 '19 at 18:54

forpas

160,666
10
38
76

This looks promising. I just need to limit it to the user list (something like `WHERE u.id in (1,2,3)) - I guess. – Paul Pushkin Mar 13 '19 at 19:02
Seems to work. I need to verify it on different user lists. – Paul Pushkin Mar 13 '19 at 19:16
I will accept this answer (second query). Though it's not difficult to compose this query, is there any way to get rid of `= 3` (since it's redundant) somehow, without making it too complex? – Paul Pushkin Mar 13 '19 at 19:34
1

I guess if that list was the result of a query, it could be replaced by `select count(*) from ...` – forpas Mar 13 '19 at 19:36
You can prepend `with ids(id) as (select 1 union select 2 union select 3)` to the query and use that twice in the query body. – Lukas Eder Mar 13 '19 at 19:37
@LukasEder This would need mysql 8.0 – forpas Mar 13 '19 at 19:40
That's OK. Not really a big deal to count the IDs in application. – Paul Pushkin Mar 13 '19 at 19:44

Salman A · Answer 3 · 2019-03-13T21:37:42.113

1

Let us rephrase the problem: find restaurants that serve at least one dish for each user. It could be expressed as:

SELECT *
FROM restaurants
WHERE id IN (
    SELECT restaurants_dishes.restaurant_id
    FROM restaurants_dishes
    JOIN users_dishes ON restaurants_dishes.dish_id = users_dishes.dish_id
    WHERE users_dishes.user_id IN (1, 2, 3)         -- <--------------+
    GROUP BY restaurants_dishes.restaurant_id       --                |
    HAVING COUNT(DISTINCT users_dishes.user_id) = 3 -- this matches --+
)

edited Mar 13 '19 at 21:37

answered Mar 13 '19 at 19:25

Salman A

262,204
82
430
521

Thank you for your answer. But in case of `user_id IN (1, 2)` it should return (Ali, Alfonso, BurgerQueen). For `user_id IN (2, 3)` it should return (Ali, Alfonso, Mario). Your query returns an empty set in both cases. – Paul Pushkin Mar 13 '19 at 19:41

SQL: Find intersection in a double many-to-many relatiion

3 Answers3