SQL get the products with more attributes in common with another product
I have three tables like follows:
products
id | name
1 | name1
2 | name2
3 | name3
attributes
id | name
1 | 开发者_高级运维 attr1
2 | attr2
3 | attr3
4 | attr4
5 | attr5
6 | attr6
7 | attr7
attr_rel Makes the relationship between prodcuts and attributes
attr_id | prod_id
1 | 1
1 | 2
2 | 1
2 | 2
3 | 3
4 | 2
4 | 3
5 | 1
5 | 2
5 | 3
What I want is to perform a query that returns all products ordered by the number of attributes they have in common with a given product.
Example: For product 3 query should return something like
id | name | num_attr_in_common
2 | product2 | 2
1 | product1 | 1
(Because product 3 shares attributes 4 and 5 with product 2 but only share attribute 5 with product 1)
Can anyone give me an help please?
select a1.prod_id,p.name,count(*) as num_attr_in_common
from attr_rel as a1
inner join (
select attr_id
from attr_rel
where prod_id = 3) as a2
on a1.attr_id = a2.attr_id and a1.prod_id <> 3
inner join products as p on p.id = a1.prod_id
group by a1.prod_id
order by num_attr_in_common desc
精彩评论