MySQL Relational Division

Question

I am having difficulties to solve one exercise:

For which People there is a Restaurant, that serves ALL their favorite beers.

(Yes, we actually have this in school :D)

I have got 2 Tables that can be used:

• Table1: Favoritebeer (Name, Surname, beername)
• Table2: OnStock (beername, restaurant, quantity)

My solution would be: OnStock % Favoritebeer

There is no such thing like DIVISION in MySQL. Any ideas how I could solve that? I found the following on Wikipedia: http://en.wikipedia.org/wiki/Relational_algebra#Division_.28.C3.B7.29 which is exactly what I need but I am having difficulties to translate it in SQL.

EDIT:

Here sample data: http://www.sqlfiddle.com/#!2/34e00

The result should be:

Bucher Rolf
Mastroyanni Pepe
Meier Hans
Meier Hanspeter
Meier Hansruedi
Müller Heinrich
Peters Peter
Zarro Darween

Westmorland

Can you reformulate your question? I don't understand what are you trying to accomplish. – Conal

Maybe add some sample data on sqlfiddle – Ideation

Sorry. My English is not very good. Each customer has one or more favorite beers. Now I need to find all customers, where a restaurant exists that serve ALL of the customer's favorite beers. – Westmorland

I edited my previous question and added an sqlfiddle to it. – Westmorland

Answer 1

Give this a try:

SELECT DISTINCT fb1.name, fb1.surname FROM favoriteBeer fb1
JOIN stock s ON fb1.beerName = s.beerName
GROUP BY fb1.name, fb1.surname, s.restaurant
HAVING COUNT(*) = (
  SELECT COUNT(*) FROM favoriteBeer fb2
  WHERE fb1.name = fb2.name AND fb1.surname = fb2.surname
)

Output:

|        NAME |   SURNAME |
|-------------|-----------|
|      Bucher |      Rolf |
| Mastroyanni |      Pepe |
|       Meier |      Hans |
|       Meier | Hanspeter |
|       Meier | Hansruedi |
|      Müller |  Heinrich |
|      Peters |     Peter |
|       Zarro |   Darween |

Fiddle here.

Lutetium

Genius. I have tried similar but I always grouped by name and surname. Never took restaurant into the grouping. And therefore I always got empty sets... – Westmorland

Right, it has to be there. Anyway, I'm pretty sure there are other ways to calculate this with a couple of NOT INs – Lutetium