mysql - SQL- Selecting the most similar product -

alright, have relation stores 2 keys, product id , attribute id. want figure out product similar given product. (attributes numbers makes example more confusing have been changed letters simplify visual representation.)

prod_att

product | attributes      1   |            1   |    b      1   |    c      2   |         2   |    b      2   |    d      3   |         3   |    e      4   |      

initially seems simple, select attributes product has , count number of attributes per product shared. result of compared number of attributes product has , can see how similar 2 products are. works products large number of attributes relative compared products, issues arise when products have few attributes. example product 3 have tie every other product (as common).

select product, count(attributes)   prod_att   attributes in   (select attributes   prod_att   product = 1)   group product ;   

any suggestions on how fix or improvements current query?
thanks!

*edit: product 4 return count() =1 products. show product 3 more similar has fewer differing attributes.

try this

select    a_product_id,    coalesce( b_product_id, 'no_matchs_found' ) closest_product_match (   select      *,       @row_num := if(@prev_value=a_product_id,@row_num+1,1) row_num,     @prev_value := a_product_id        (select @prev_value := 0) r     join (         select           a.product_id a_product_id,          b.product_id b_product_id,          count( distinct b.attributes ),          count( distinct b2.attributes ) total_products                   products           left join products b on ( a.attributes = b.attributes , a.product_id <> b.product_id )           left join products b2 on ( b2.product_id = b.product_id )        /*where */          /*  a.product_id = 3 */         group          a.product_id,          b.product_id         order            1, 3 desc, 4   ) t ) t2     row_num = 1 

the above query gets closest matches products, can include product_id in innermost query, results particular product_id, have used left join if product has no matches, displayed

sqlfiddle

hope helps