给定SELECT语句，我想通过多个子查询得到结果RECIPE_ID？

Question

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:

SELECT 
    `recipe`.`recipeId` AS recipe_id ,
    (select count(`recipe_ingredient`.ingredientId) from `recipe_ingredient` where `recipe`.recipeId = `recipe_ingredient`.recipeId) as ingredientCount ,  
    IF(
        (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId)>99                      
        ,100
        ,round( (select (3*100 / count(DISTINCT `recipe_ingredient`.ingredientId)) from `recipe_ingredient` where `recipe_ingredient`.recipeId = `recipe`.recipeId) ) 
    ) 
    as match_percentage , 
    GROUP_CONCAT(
        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( 
                SELECT 
                `recipe_ingredient`.`recipeId`
                FROM `recipe_ingredient` 
                WHERE `recipe_ingredient`.`ingredientId` 
                IN(
                  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 : http://sqlfiddle.com/#!2/f4983/2

Answer 1

Is this what you want? Please Check it with other scenario.

http://sqlfiddle.com/#!2/f4983/8