Блог

У меня есть таблица пользователей с идентификатором пользователя и именем пользователя. В другой таблице есть список интересов с идентификатором интереса и именем. Третья таблица — это таблица соединений с идентификаторами пользователя и интереса.

Для каждой пары пользователей я хочу получить количество общих интересов, которые у них есть. Я перепробовал много чего, самое последнее — это:

 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
  

Я просто не могу получить рабочий запрос по этому поводу. Любая помощь?

Это самосоединение на 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;
  

Примечание: эта версия возвращает только идентификаторы и только одну пару для двух пользователей: (a, b), но не (b, a).

Теперь это становится сложнее, если вы хотите включить пары пользователей, у которых нет общих интересов. Если это так, вам нужно сначала сгенерировать пары пользователей, используя cross join и the, чтобы указать интересы:

 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;