CODEWITHSUNDEEP
mysqlsql
Clinton J
Clinton J

Reputation: 2123

getting common joins for every combination of entries from a table

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

I just can't get a working query on this. Any help?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

This is a self join on interests_join:

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;

Upvotes: 1

Related Questions