MySQL公司和部门关系,MySQL关系部门

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:

The result should be:

Bucher Rolf

Mastroyanni Pepe

Meier Hans

Meier Hanspeter

Meier Hansruedi

Müller Heinrich

Peters Peter

Zarro Darween

解决方案

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.