How to select by two values from same column?-CodePudding

I have a simple db which contains users and their hobbies. Relation is many-to-many, so there are three tables. I need to select all users by two specific hobbies. For example they should do both football and basketball.

I'm new to SQL and thought that AND operator will solve it, but it doesn't work for me. My query is

SELECT u.name
FROM hobby h
INNER JOIN user_hobby uh on h.id = uh.hobby_id
INNER JOIN user u on u.id = uh.user_id
WHERE h.name = 'Basketball' AND h.name = 'Football';

I tried a couple of variations - no result. I know it might be easy, sorry for asking that type of question, but just didn't find a proper example of my case in the internet, so will be thankful for any help!

CodePudding user response：

If you want to select all users that play either basketball or football:

SELECT u.name
FROM hobby h
INNER JOIN user_hobby uh on h.id = uh.hobby_id
INNER JOIN user u on u.id = uh.user_id
WHERE h.name IN('Basketball', 'Football');

If you want to select all users that play both basketball and football:

SELECT N.name
FROM
(
    SELECT u.name, h.name AS 'Hobby_Name', COUNT(*) AS 'Hobby_Count'
    FROM hobby h
    JOIN user_hobby uh on h.id = uh.hobby_id
    JOIN user u on u.id = uh.user_id
    WHERE h.name IN('Basketball', 'Football')
    GROUP BY u.name, h.name
) N
GROUP BY N.name
HAVING COUNT(*) = 2

CodePudding user response：

Use this. It works for me and I use it often.

SELECT u.name
FROM hobby h
INNER JOIN user_hobby uh on h.id = uh.hobby_id
INNER JOIN user u on u.id = uh.user_id
WHERE h.name in ('Basketball', 'Football');