Schemas and data in question are:
Code: Select all
order_lists:
+--------------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------+------+-----+---------+-------+
| order_id | int(5) | NO | PRI | NULL | |
| menu_item_id | int(5) | NO | PRI | NULL | |
| quantity | int(5) | NO | | NULL | |
+--------------+--------+------+-----+---------+-------+
+----------+--------------+----------+
| order_id | menu_item_id | quantity |
+----------+--------------+----------+
| 3 | 2 | 1 |
| 3 | 3 | 1 |
| 3 | 6 | 3 |
+----------+--------------+----------+
menu_items_ingredients:
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| menu_item_id | int(10) | NO | PRI | NULL | |
| ingredient_id | int(10) | NO | PRI | NULL | |
| quantity | int(5) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
+--------------+---------------+----------+
| menu_item_id | ingredient_id | quantity |
+--------------+---------------+----------+
| 2 | 1 | 50 |
| 2 | 15 | 100 |
| 2 | 17 | 150 |
| 2 | 18 | 3 |
| 3 | 1 | 150 |
| 3 | 3 | 150 |
| 3 | 4 | 1 |
| 6 | 2 | 150 |
| 6 | 12 | 150 |
| 6 | 13 | 100 |
| 6 | 14 | 100 |
+--------------+---------------+----------+
Code: Select all
SELECT `menu_items_ingredients`.`ingredient_id` , SUM( `menu_items_ingredients`.`quantity` )
FROM `menu_items_ingredients`
WHERE `menu_items_ingredients`.`menu_item_id`
IN (
SELECT `order_lists`.`menu_item_id`
FROM `order_lists`
WHERE `order_id` =3
)
GROUP BY `ingredient_id`
Code: Select all
SELECT `menu_items_ingredients`.`ingredient_id` , SUM( `menu_items_ingredients`.`quantity` ) * `order_lists`.`quantity`
FROM `menu_items_ingredients` , `order_lists`
WHERE `menu_items_ingredients`.`menu_item_id` = `order_lists`.`menu_item_id`
AND `menu_items_ingredients`.`menu_item_id`
IN (
SELECT `order_lists`.`menu_item_id`
FROM `order_lists`
WHERE `order_id` =3
)
GROUP BY `ingredient_id`
Thanks.