extract customers with same hobbies with mysql query

Question

customer id | hobby

I would like to use mysql query to extract customers which have the same hobbies.
If someone has more hobbies we have one row for each hobby.

It is easy to use

SELECT customer_id 
FROM customers
WHERE hobby='football'

My problem is that I want to search for two hobbies.

For example 'football' and 'swimming' and the query must result only the customers which have both.

Is it possible to do it with query or should I use stored procedure and how ?

WHERE IN () GROUP BY HAVING COUNT(*) = number of items in IN() — Strawberry, Apr 21 '14 at 15:25
possible duplicate of [How to return rows that have the same column values in MySql](http://stackoverflow.com/questions/16704290/how-to-return-rows-that-have-the-same-column-values-in-mysql) — Barmar, Apr 21 '14 at 15:27
Do you want customers who have *only* those two hobbies, or *at least* those two hobbies? — Bohemian, Apr 29 '14 at 14:06

juergen d · Answer 1 · 2014-04-21T19:16:39.797

0

SELECT customer_id 
FROM customers 
WHERE hobby in ('swimming','football')
group by custimer_id
having count(distinct hobby) = 2

edited Apr 21 '14 at 19:16

answered Apr 21 '14 at 15:27

juergen d

1

It seems to me extraordinarily unlikely that one customer can have the same hobby twice, but OK – Strawberry Apr 21 '14 at 15:29
Just to be sure, sure :) – juergen d Apr 21 '14 at 15:31
(unless that hobby happens to be talking to oneself) – Strawberry Apr 21 '14 at 17:00
you select something different than you group by. – juergen d Apr 21 '14 at 18:11
It works for me. See [this SQLFiddle](http://sqlfiddle.com/#!2/528a45/2). Your example data is weird though. I changed one value in my example. – juergen d Apr 21 '14 at 19:16

1 Answers1