Tony Cross Tony Cross - 6 months ago 9
SQL Question

How can a query be refined to first do a check on another table before performing its main query?

I have a query now properly pulling scored matches of like interests between different users given a starting userid.

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


How can I adapt this easily to first do a check for proper country of users in a different
users
table, before running the interest matching query so that it only runs the matching query if they are in the proper country?

The starting
userid
should be '1', and the proper
country
should be '1'

The proper returned result set should be:

-------
userid
-------
3


Sample test data

CREATE TABLE `users` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`country` int(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `users` SET `id` = '1', `country` = '1';
INSERT INTO `users` SET `id` = '2', `country` = '2';
INSERT INTO `users` SET `id` = '3', `country` = '1';
INSERT INTO `users` SET `id` = '4', `country` = '4';

CREATE TABLE `users_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 `users_interests` set `userid` = '1', `interest` = 'term1';
INSERT INTO `users_interests` set `userid` = '1', `interest` = 'term2';
INSERT INTO `users_interests` set `userid` = '1', `interest` = 'term3';
INSERT INTO `users_interests` set `userid` = '1', `interest` = 'term4';
INSERT INTO `users_interests` set `userid` = '2', `interest` = 'term1';
INSERT INTO `users_interests` set `userid` = '2', `interest` = 'term2';
INSERT INTO `users_interests` set `userid` = '2', `interest` = 'term8';
INSERT INTO `users_interests` set `userid` = '2', `interest` = 'term4';
INSERT INTO `users_interests` set `userid` = '3', `interest` = 'term9';
INSERT INTO `users_interests` set `userid` = '3', `interest` = 'term2';
INSERT INTO `users_interests` set `userid` = '3', `interest` = 'term3';
INSERT INTO `users_interests` set `userid` = '3', `interest` = 'term7';
INSERT INTO `users_interests` set `userid` = '4', `interest` = 'term1';
INSERT INTO `users_interests` set `userid` = '4', `interest` = 'term2';
INSERT INTO `users_interests` set `userid` = '4', `interest` = 'term3';
INSERT INTO `users_interests` set `userid` = '4', `interest` = 'term4';

Answer

I suggest to deal with 4 tables :

  • users u1 : user 1
  • users u2 : user 2 (same country as user 1)
  • users_interest i1 : interests of user 1
  • users_interest i2 : interests of user 2 (same interest as user 1)

Then, you can retrieve common interests and make filters for each table. The request do what you want :

SELECT u2.id, count(*) 
FROM users u1
INNER JOIN users u2
   ON u1.id <> u2.id
  AND u1.country = u2.country
INNER JOIN users_interests i2
   ON i2.userid = u2.userid
  AND i2.interest IN (SELECT interest FROM users_interests
                      WHERE userid = u1.id)
WHERE u1.userid = 1
GROUP BY u2.id
ORDER BY COUNT(*) DESC
LIMIT 0,10

If you want to retrieve all common interests of all users, you just have to delete the

WHERE u1.userid = 1

And change :

SELECT u1.id sourceid, u2.id targetid, count(*)

GROUP BY u1.id, u2.id