Joe Wadsworth Dont Think BIG Joe Wadsworth Dont Think BIG - 6 months ago 14
SQL Question

How to combine these queries together as one result

SELECT COUNT(recipe_id) AS Found
FROM recipe_ingredients R, users_ingredients U
WHERE R.key_ingredient = U.key_ingredient;

SELECT recipe_id, COUNT(recipe_id) As Count FROM recipe_ingredients GROUP BY recipe_id;

Answer

Based on your comments what I understand is you need to show three columns as recipeId, foundIngredients, countIngredients.

Since with out table schema I assumed the structure and derive the below query:

SQL Fiddle is http://sqlfiddle.com/#!9/eee151/3

SELECT COU.recipe_id, IFNULL(FOU.Found, 0) AS Found, COU.Count
FROM  ( SELECT recipe_id, COUNT(recipe_id) As Count 
        FROM recipe_ingredients 
        GROUP BY recipe_id) COU
LEFT OUTER JOIN ( SELECT R.recipe_id, COUNT(R.key_ingredient) AS Found
        FROM users_ingredients U
        JOIN recipe_ingredients R ON R.key_ingredient = U.key_ingredient
        GROUP BY R.recipe_id) FOU ON FOU.recipe_id = COU.recipe_id
Comments