user2253345 user2253345 - 7 months ago 23
SQL Question

Count similarities MySQL

i have been trying to figure out a query and it goes likes this:

I have a table ingredient containing information about ingredients:

table ingredient
id int pk
name varchar(50)

I have a table category, every ingredient can have one or more category connected to it, for example ice cream could be related to the categories dessert, cold dish and so on. So a many to many relation.

table category
id int pk
name varchar(50)

table category_ingredient
category_id int
ingredient_id int

Lets say i want to make a query about how similar two ingredients are by measuring the categories they share. What I mean is, lets say my database contains 3 ingredients: 1,2,3. Ingredient 1 is connected to categories {1,2,3}, ingredient 2 is connected to categories {3,4,5} and ingredient 3 is connected to categories {1,2,3,4}.

Doing the query where I would search for the ingredients most similar to ingredient 1, would result in:

Ingredient 3 100%
Ingredient 2 33%

Any thought on how this kind of query would be achieved?

SELECT t2.ingredient_id,
    100 * SUM(CASE WHEN t1.category_id = t2.category_id THEN 1 ELSE 0 END) /
    (SELECT COUNT(*) FROM category_ingredient WHERE ingredient_id = 1) AS commonCount
FROM cateogory_ingredient t1
INNER JOIN category_ingredient t2
    ON t1.ingredient_id = 1
GROUP BY t2.ingredient_id