Search code examples
mysqlrelational-division

MySQL Relational Division

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
Solution

  • 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.