how do I get a count of matches across multiple tables joining by id?

Question

How can I 'score' results using order by, if possible, by number of matches between a given user and every other user in a table.

Basically, I have a given 'userid' of '1' and I need to check all of this users 'interests' against of users interests and order by the number of like matches between users.

Say userid '1' has 4 interests in common with userid '4', but only 3 interests in common with userid '2', it would return 4 first, then 2, then everyone else with less overall interest matches.

I have tried about 10 different queries to no avail, not even one that was close, which is why I didn't post any of the attempts here.

CREATE TABLE `interests` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `userid` int(12) NOT NULL DEFAULT '0',
  `interest` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `interests` set `userid` = '1', `interest` = 'term1';
INSERT INTO `interests` set `userid` = '1', `interest` = 'term2';
INSERT INTO `interests` set `userid` = '1', `interest` = 'term3';
INSERT INTO `interests` set `userid` = '1', `interest` = 'term4';
INSERT INTO `interests` set `userid` = '2', `interest` = 'term1';
INSERT INTO `interests` set `userid` = '2', `interest` = 'term2';
INSERT INTO `interests` set `userid` = '2', `interest` = 'term8';
INSERT INTO `interests` set `userid` = '2', `interest` = 'term4';
INSERT INTO `interests` set `userid` = '3', `interest` = 'term9';
INSERT INTO `interests` set `userid` = '3', `interest` = 'term2';
INSERT INTO `interests` set `userid` = '3', `interest` = 'term3';
INSERT INTO `interests` set `userid` = '3', `interest` = 'term7';
INSERT INTO `interests` set `userid` = '4', `interest` = 'term1';
INSERT INTO `interests` set `userid` = '4', `interest` = 'term2';
INSERT INTO `interests` set `userid` = '4', `interest` = 'term3';
INSERT INTO `interests` set `userid` = '4', `interest` = 'term4';

The proper result set should look like:

-------
userid
-------
4
2
3

Any help here is greatly appreciated guys.

Answer 1

You can do a SELF JOIN:

SELECT i2.userid
FROM interests i1
INNER JOIN interests i2
  ON i2.userid <> i1.userid
  AND i2.interest = i1.interest
WHERE i1.userid = 1
GROUP BY i2.userid
ORDER BY COUNT(*) DESC;

ONLINE DEMO