CODEWITHSUNDEEP
mysqlsql
Muggles
Muggles

Reputation: 1556

Count amount of matches within a subquery to order the query by

I am trying to count the amount of matches within a subquery to order the results by.

Query I am trying:

SELECT recipes.*, (SELECT COUNT(ingredient_id) FROM recipes WHERE ingredient_id IN(1,3))            AS ingredient_matches 
FROM recipes 
INNER JOIN ingredients 
ON recipes.id = ingredients.recipe_id 
INNER JOIN ingredients_available 
ON ingredients.ingredient_id = ingredients_available.id
GROUP BY recipes.id 
ORDER BY ingredient_matches DESC;

Database structure:

recipes:
id, name
ingredients:
id, recipe_id, ingredient_id
ingredients_available:
id, name
tags:
id, recipe_id, tag_id
tags_available:
id, name

The query below works but I want to be able to have access ingredients that are not matched so I can say they need them if that makes sense? 

SELECT recipes.*, COUNT(ingredients.id) AS ingredient_matches 
FROM recipes 
INNER JOIN ingredients 
ON recipes.id = ingredients.recipe_id 
INNER JOIN ingredients_available 
ON ingredients.ingredient_id = ingredients_available.id
WHERE ingredient_id IN (1, 5) 
GROUP BY recipes.id 
ORDER BY ingredient_matches DESC;

Upvotes: 0

Views: 69

Answers (2)

Muggles
Muggles

Reputation: 1556

http://sqlfiddle.com/#!2/3f9ef/15

Was the desired result. It is because I didn't make sure recipes_id = ingredients.recipe_id within the subquery.

Upvotes: 1

SS781
SS781

Reputation: 2649

Does the below work?:

SELECT recipes.*, COUNT(ingredients.id) AS ingredient_matches 
FROM recipes 
INNER JOIN ingredients 
ON recipes.id = ingredients.recipe_id 
LEFT JOIN ingredients_available 
ON ingredients.ingredient_id = ingredients_available.id
WHERE ingredient_id IN (1, 5) 
GROUP BY recipes.id 
ORDER BY ingredient_matches DESC;

Upvotes: 0

Related Questions