CODEWITHSUNDEEP
mysql
Zaid Kajee
Zaid Kajee

Reputation: 712

Mysql, join query , confused

the problem i face is , i would like to join these two queries.

   SELECT recipes.RecipeID ,recipes.Name,recipes.ImageThumb,recipes.RatingTotal,
   count(ratings.RecipeID) AS trates  
   FROM recipes 
        JOIN ratings 
            ON ratings.RecipeID = recipes.RecipeID 
   WHERE recipes.Name LIKE '%ade%'  
   ORDER BY Name;

this query does not work as desired , it brings in 1 result when there are more then one , and the trates comes as the total of all the results , not just for that particular RecipeID

Upvotes: 3

Views: 42

Answers (1)

Omesh
Omesh

Reputation: 29081

I think this is what you are looking for:

SELECT recipes.RecipeID ,recipes.Name,recipes.ImageThumb,recipes.RatingTotal,
       COUNT(ratings.RecipeID) AS trates  
FROM recipes 
     LEFT JOIN ratings 
         ON ratings.RecipeID = recipes.RecipeID 
WHERE recipes.Name LIKE '%ade%'  
GROUP BY RecipeID 
ORDER BY Name;

Upvotes: 3

Related Questions