CODEWITHSUNDEEP
mysql
user1767586
user1767586

Reputation: 458

Get entries which share other entires in an associated table?

I have two tables: users and interests.

interests consists of an interest (e.g. "football") and a user id.

I would like to query a user and get all other users who shares at least 3 interests but I don't know how to do this efficiently.

How do I write SQL for "get user's interests and fetch all other users who shares at least three of those interests"?

Thanks.

Upvotes: 0

Views: 35

Answers (1)

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT i.interest, i.user_id, COUNT(*) AS amount
# , u.username
FROM interests AS i
LEFT JOIN interests AS i2
ON i.interest LIKE i2.interest
# LEFT JOIN users AS u
# ON i2.user_id = u.id
WHERE i.user_id = 1
GROUP BY i.interest
HAVING amount >= 3

This would asume that you have users (id, username) and `interests (interest, user_id)´. Uncomment for including username.

Consider also having interests as ID references instead of VARCHAR. It's more logical to maintain.

Upvotes: 1

Related Questions