CODEWITHSUNDEEP
mysqljoinrelational-division
Chloe
Chloe

Reputation: 55

MySQL how to get the group that match the value of another group in the same column?

Here are the tables:  

  Drinker (name, address)  
  Bar (name, address)  
  Beer (name, brewer)  
  Frequents (drinker, bar, times_a_week)  
  Likes (drinker, beer)  
  Serves (bar, beer, price)

And, the question goes like this:
Find those drinkers who enjoy exactly the same set of beers as Silvie Hume.

I have my code as following:

SELECT *  
FROM drinker d  
JOIN likes l1 ON d.Name = l1.Drinker  
WHERE l1.Beer IN (SELECT Beer FROM likes l2 WHERE l2.Drinker = 'Silvie Hume');

It seems like my output return all drinks that match one of the beers Silvie Hume like, I have no idea how to select the drinker who like the same set of beer as Silvie Hume. Any help? Thanks!
I tried to replace IN with = ALL, and it didnt work as well

Upvotes: 1

Views: 82

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

One option uses a self join on the Likes table:

SELECT
    t1.drinker
FROM Likes t1
LEFT JOIN Likes t2
    ON t1.beer = t2.beer AND t2.drinker = 'Silvie Hume'
WHERE
    t1.drinker <> 'Silver Hume'
GROUP BY
    t1.drinker
HAVING
    COUNT(*) = COUNT(t2.beer) AND
    COUNT(*) = (SELECT COUNT(*) FROM Likes WHERE drinker = 'Silvie Hume');

For an explanation, the first condition in the HAVING clause asserts that all a given drinker's likes match those of Silvie Hume. The second condition covers the edge case where all of a given drinker's likes do match, but Silvie Hume had some other likes as well. We therefore assert that the number of likes for each drinker is the same as Silvie Hume.

Upvotes: 1

Related Questions