#StackBounty: #mysql #sql Select max value on multiple tables, without counting them twice

Bounty: 350

I’m making a query that allows me to order recipes by score.

Tables structure

Structure is that a flyer contains one or many flyer_items, which can contain one or many ingredients_to_flyer_item (this table links ingredient to the flyer item). The other table ingredient_to_recipe links the same ingredients but to one or many recipes. Link to .sql file is included at the end.

Example query

I want to get recipe_id and a SUM of the MAX price weight of each ingredient that are part of the recipe (linked by ingredient_to_recipe), but if a recipe has multiple ingredients that belongs to the same flyers_item, it should be counted once.

SELECT itr.recipe_id,
       SUM(itr.weight),
       SUM(max_price_weight),
       SUM(itr.weight + max_price_weight) AS score
FROM
  ( SELECT MAX(itf.max_price_weight) AS max_price_weight,
           itf.flyer_item_id,
           itf.ingredient_id
   FROM
     (SELECT ifi.ingredient_id,
             MAX(i.price_weight) AS max_price_weight,
             ifi.flyer_item_id
      FROM flyer_items i
      JOIN ingredient_to_flyer_item ifi ON i.id = ifi.flyer_item_id
      WHERE i.flyer_id IN (1,
                           2)
      GROUP BY ifi.ingredient_id ) itf
   GROUP BY itf.flyer_item_id) itf2
JOIN `ingredient_to_recipe` AS itr ON itf2.`ingredient_id` = itr.`ingredient_id`
WHERE recipe_id = 5730
GROUP BY itr.`recipe_id`
ORDER BY score DESC
LIMIT 0,10

The query almost works fine, because most of the results are good, but for some lines, some ingredients are ignored and not counted from the score as they should.

Test cases

| recipe_id | 'score' with current query | what 'score' should be | explanation                                                                 |
|-----------|----------------------------|------------------------|-----------------------------------------------------------------------------|
| 8376      | 51                         | 51                     | Good result                                                                 |
| 3152      | 1                          | 18                     | Only 1 ingredient having a score of one is counted, should be 4 ingredients |
| 4771      | 41                         | 45                     | One ingredient worth score 4 is ignored                                     |
| 10230     | 40                         | 40                     | Good result                                                                 |
| 8958      | 39                         | 39                     | Good result                                                                 |
| 4656      | 28                         | 34                     | One ingredient worth 6 is ignored                                           |
| 11338     | 1                          | 10                     | 2 ingredients, worth 4 and 5 are ignored                                    |

I have a very difficult time finding an easy way to explain it. Let me know if anything else could help.

Here is a link to the demo database to run the query, test examples and test cases: https://nofile.io/f/F4YSEu8DWmT/meta.zip

Thank you very much.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.