Finding the mutual interests-CodePudding

Users Table

Interests Table

user_id    interest_id
--------   -----------
1            34
2            34
3            34
2            45
1            45
3            46
4            47
5            34
6            78

Expected Result (say I am the user with the id of 1)

user_id      mutual_interest_count
--------     ---------------------
2                    2
3                    1
4                    0
5                    1
6                    0

How can I build the query finding the mutual interests?

CodePudding user response：

We can use a self join with aggregation approach here:

SELECT u.id, COALESCE(t.cnt, 0) AS mutual_interest_count
FROM Users u
LEFT JOIN
(
    SELECT t2.user_id, COUNT(*) AS cnt
    FROM Interests t1
    INNER JOIN Interests t2 ON t2.interest_id = t1.interest_id
    WHERE t1.user_id = 1 AND t2.user_id <> 1
    GROUP BY t2.user_id
) t
    ON t.user_id = u.id
WHERE u.id <> 1;

Here is a working demo on MySQL 8.

CodePudding user response：

SELECT Users.id, COUNT(t2.user_id) mutual_interest_count
FROM Users
LEFT JOIN Interests t1 ON t1.user_id = Users.id
LEFT JOIN Interests t2 ON t1.interest_id = t2.interest_id
                      AND t2.user_id = 1
WHERE Users.id <> 1
GROUP BY Users.id

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ea982860620f489855edca217c0169ae