2

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
Chris
  • 3,581
  • 8
  • 30
  • 51
  • Can you reformulate your question? I don't understand what are you trying to accomplish. – Babblo Nov 30 '13 at 16:27
  • Maybe add some sample data on [sqlfiddle](http://www.sqlfiddle.com) – Filipe Silva Nov 30 '13 at 16:30
  • 1
    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. – Chris Nov 30 '13 at 16:33
  • I edited my previous question and added an sqlfiddle to it. – Chris Nov 30 '13 at 16:55

1 Answers1

5

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.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • 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... – Chris Nov 30 '13 at 17:04
  • Right, it has to be there. Anyway, I'm pretty sure there are other ways to calculate this with a couple of `NOT IN`s – Mosty Mostacho Nov 30 '13 at 17:06