i have a sql problem with this schemas :
table user(id, name)
table page(id, name)
table page_user(id_page, id_user)
one page can be liked by multiple users and one user can like multiple pages
i would like to select all users in my table with a column with the number of liked page in common with some fixe user
like :
id_user | page_in_common_with_user_#id#_count
Assuming that "liked page" refers to the page_user
table, then you can use a self join:
select pu.id_user, count(pux.id_page) as PagesInCommonWithX
from page_user pu left join
page_user pux
on pu.id_page = pux.id_page and
pux.id_user = $x
group by pu.id_user;