Count similarities MySQL -

i have been trying figure out query , goes likes this:

i have table ingredient containing information ingredients:

table ingredient id int pk name varchar(50) 

i have table category, every ingredient can have 1 or more category connected it, example ice cream related categories dessert, cold dish , on. many many relation.

table category id int pk name varchar(50)  table category_ingredient category_id int ingredient_id int 

lets want make query how similar 2 ingredients measuring categories share. mean is, lets database contains 3 ingredients: 1,2,3. ingredient 1 connected categories {1,2,3}, ingredient 2 connected categories {3,4,5} , ingredient 3 connected categories {1,2,3,4}.

doing query search ingredients similar ingredient 1, result in:

 ingredient 3 100%  ingredient 2 33% 

any thought on how kind of query achieved?

the "nice" way (in mind) query use conditional aggregation involving case when ... in expression, mysql not seem support this. instead, cross join on first ingredient against entire category_ingredient table , count number of categories in common, each ingredient. number, divided total number of categories in first ingredient, yields percentage in common.

select t2.ingredient_id,     100 * sum(case when t1.category_id = t2.category_id 1 else 0 end) /     (select count(*) category_ingredient ingredient_id = 1) commoncount category_ingredient t1 inner join category_ingredient t2     on t1.ingredient_id = 1 group t2.ingredient_id