I have a table of users with userid and username.
Another table has a list of interests, with interestid and name.
A third table is a join table, with userid and interestid.
For each pair of users, I want to get the count of interests they have in common. I've tried a lot of things, the most recent is this:
SELECT u1.username AS me, u2.username AS you, COUNT(j.interestid) AS commoninterests
FROM users u1, users u2
INNER JOIN interests_join j
ON u1.id = j.id
WHERE u1.id != u2.id
GROUP BY u1.name
This is a self join on
select ij1.userid, ij2.userid, count(*) from interests_join ij1 join interests_join ij2 on ij1.interestid = ij2.interestid and ij1.userid < ij2.userid group by ij1.userid, ij2.userid;
Note: this version only brings back the ids and only one pair for two users: (a, b) but not (b, a).
Now, this gets trickier if you want to include user pairs that have no common interests. If so, you need to first generate the user pairs using a
cross join and the bring in the interests:
select u1.username, u2.username, count(ij2.userid) from users u1 cross join users u2 left join interests_join ij1 on ij1.userid = u1.userid left join interests_join ij2 on ij2.userid = u2.userid and ij1.interestid = ij2.interestid group by u1.username, u2.username;