select user with 5/10/15 matching keywords

Question

I have tables like: user(id, name), interests(id, interest) and users_interests(user_id, interest_id).

I want to find all users that have a given number of matching interests.

Let's say there are 4 users: (mike, andre, julian, john). 2 of them have 3 matching interests. For example, mike(music, sports, games), andre(music, sports, games). The other 2 users have different interests. I need a query that will give me all users with 2 matching interests (2 is variable). The result would be mike and andre.

Answer 1

Note that this answer borrows heavily from an answer to a very similar question.

SELECT
    i.user_id,
    GROUP_CONCAT(i.interest ORDER BY i.interest) AS common_interests
FROM
    (SELECT u_i.user_id, i.interest
    FROM interest AS i
    JOIN users_interests AS u_i 
    ON i.id = u_i.interest_id) AS i
        JOIN
        (SELECT u_i.user_id, i.interest
    FROM interest AS i
    JOIN users_interests AS u_i 
    ON i.id = u_i.interest_id) AS i2
        ON i.interest = i2.interest
        AND i.user_id != i2.user_id
GROUP BY i.user_id , i2.user_id
HAVING COUNT(i.interest) = 3;

http://sqlfiddle.com/#!2/dcff0/6

I included the common_interests column just to illustrate the usefulness of the GROUP_CONCAT() function for cases such as this. It's not necessary to add an order to the function, but it could be useful if you wanted to go ever further down the rabbit hole and look at an aggregate result such as "all sets of shared interests, and their corresponding users":

http://sqlfiddle.com/#!2/dcff0/21

But unless your dataset is trivially small, that's going to be a generally impractical query to say the least.