Multiple-table SELECT query help [SOLVED]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Multiple-table SELECT query help [SOLVED]

Post 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.
Last edited by jayshields on Thu Jul 03, 2008 7:32 am, edited 1 time in total.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Multiple-table SELECT query help

Post 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 ???
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Multiple-table SELECT query help

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Multiple-table SELECT query help

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Multiple-table SELECT query help

Post 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!
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Multiple-table SELECT query help

Post by califdon »

jayshields wrote:Thanks alot calif!
:) Glad you got it working!
Post Reply