2015-02-18 06:08 阅读 59


I have ingredient.ingredientId is 709,710,711 this id i am put in ingredient Sub-Query and AS match_percentage i am wright (3*100 / count is counting of ingredient.ingredientId , that all comes from my PHP code here i am giving this example for my solution.

Now result like those RECIPE_ID that i am pass three ingredient.ingredientId are match with this table recipe_ingredient.ingredientId and keep recipe_ingredient.recipeId

I want RECIPE_ID | 1 and 3 on result . ONLY WHO has that three ids 709,710,711 not even that two 709,710.

Here is my RECIPE_ID query:

    `recipe`.`recipeId` AS recipe_id ,
    (select count(`recipe_ingredient`.ingredientId) from `recipe_ingredient` where `recipe`.recipeId = `recipe_ingredient`.recipeId) as ingredientCount ,  
        (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId)>99                      
        ,round( (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId) ) 
    as match_percentage , 
        DISTINCT `recipe_ingredient`.ingredientId 
        ORDER BY `recipe_ingredient`.ingredientId ASC 
           ) as recipeIngredients 
from `recipe` 
left join `recipe_ingredient` on `recipe_ingredient`.recipeId = `recipe`.recipeId
left join `ingredient` on `ingredient`.ingredientId = `recipe_ingredient`.ingredientId   
where  `recipe`.`recipeId` IN( 
                FROM `recipe_ingredient` 
                WHERE `recipe_ingredient`.`ingredientId` 
                  SELECT `ingredient`.`ingredientId` AS linkIng 
                  FROM `ingredient` 
                  WHERE `ingredient`.`ingredientId` IN(709,710,711) or `ingredient`.`linkIngredientPerent` IN(709,710,711)
                GROUP BY `recipe_ingredient`.`recipeId` 
                ORDER BY `recipe_ingredient`.`recipeId` ASC

and (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId) > 24  
group by `recipe`.recipeId 

My Query Link :!2/f4983/2

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dongyu5482 dongyu5482 2015-02-18 06:30

    Is this what you want? Please Check it with other scenario.!2/f4983/8

    点赞 评论 复制链接分享