Multiple-table SELECT query help [SOLVED]
Posted: Wed Jul 02, 2008 5:26 pm
OK, I'm trying to get all the ingredients used in constructing a whole order of dishes and the amount of each ingredient to be used in total.
Schemas and data in question are:
I can get pretty close, this query fetches all the ingredients needed for all the menu items, and their quantities, but doesn't take into account the quantity of the menu item ordered:
I think I need something closer to this, but it produces some way off calculations and I can't see where the numbers are coming from
Can anyone point me in the right direction?
Thanks.
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.