Page 1 of 1

Multiple-table SELECT query help [SOLVED]

Posted: Wed Jul 02, 2008 5:26 pm
by jayshields
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:

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 |
+--------------+---------------+----------+
 
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:

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`
 
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

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`
 
Can anyone point me in the right direction?

Thanks.

Re: Multiple-table SELECT query help

Posted: Wed Jul 02, 2008 5:36 pm
by califdon
I don't think you want a SUM at all. You just want the product of order_lists.quantity times menu_items_ingredients.quantity. A SUM adds up all the quantities, regardless of what they represent - 4 eggs + 1 tbsp salt + 2 cups flour = 7 ???

Re: Multiple-table SELECT query help

Posted: Wed Jul 02, 2008 6:01 pm
by jayshields
So you mean somrthing like this?

Code: Select all

 
SELECT `menu_items_ingredients`.`ingredient_id` , `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`
That gives 1 -> 100 ... 12 -> 300, 13 -> 200, 14 -> 200. Which aren't the results I need. I need 1 -> 200 ... 12 -> 450, 13 -> 300, 14 -> 300.

I hope that makes it clearer.

Re: Multiple-table SELECT query help

Posted: Wed Jul 02, 2008 8:40 pm
by califdon
Yes, now I see why you need the SUM() function. I haven't yet tried this, but here's what I'd try first:

Code: Select all

SELECT ingredient_id, SUM(extqty)
  FROM (SELECT ing.ingredient_id, (ing.quantity * ord.quantity) AS extqty
          FROM menu_item_ingredients ing JOIN order_lists ord
          ON ing.ingredient_id = ord.ingredient_id
          WHERE order_id=3)
  GROUP BY ingredient_id
I think that's close. If I have the time, I'll try it myself.

Re: Multiple-table SELECT query help

Posted: Thu Jul 03, 2008 7:30 am
by jayshields
Hella awesome! Manage to mess around with what you gave me and got it sorted, I briefly thought about using derived tables but didn't try it because I've never used them before.

Here is the solution:

Code: Select all

 
SELECT 
  `new`.`ingredient_id`, SUM( `new`.`extqty` )
FROM 
  (
  SELECT 
    `ing`.`ingredient_id`, (`ing`.`quantity` * `ord`.`quantity`) AS `extqty`
  FROM 
    `menu_items_ingredients` `ing`
  JOIN 
    `order_lists` `ord` 
  ON 
    `ing`.`menu_item_id` = `ord`.`menu_item_id`
  WHERE 
    `order_id` =3
  ) 
  AS 
    `new`
GROUP BY 
  `ingredient_id`
Thanks alot calif!

Re: Multiple-table SELECT query help

Posted: Thu Jul 03, 2008 6:25 pm
by califdon
jayshields wrote:Thanks alot calif!
:) Glad you got it working!