[Resolved] Help with query

jdiver

I don't know why I can't wrap my brain around this query, but I'm completely stuck and feeling dumb.

I have a system basically for a cooking class. Users sign up to attend a specific class (event), and choose what menu items they would like to cook from a menu that is associated with the event they selected.

What I want to do is list how many of each menu item are being ordered for each event. I'm looping through the events with no problem. The query to get the menu items however, is only returning the item if it was ordered by someone for that event. I need to list every item for each event, even if the item shows a zero.

Database Structure:

MENUS_LISTS_TABLE
- menu_id
- item_id

ORDERS_TABLE
- order_id
- event_id
- complete

ORDER_ITEMS_TABLE
- order_id
- item_id
- quantity

Here is the query:

		$sql_i = "SELECT l.item_id, SUM(oi.quantity) AS num_event_items 
				FROM " . MENU_LISTS_TABLE . " l, " . ORDERS_TABLE . " o 
					LEFT JOIN " . ORDER_ITEMS_TABLE . " oi ON o.order_id = oi.order_id 
				WHERE l.menu_id = '$menu_id' 
				AND o.event_id = '$event_id' 
				AND o.complete = '1' 
				AND oi.item_id = l.item_id 
				GROUP BY l.item_id 
				ORDER BY l.item_order";

I know I'm just not joining on the correct fields or something simple like that. Any help would be greatly appreciated. Thanks!

rachel2004

I'm not at a location where I can test this, but give it a try:

<?php 
$sql_i = "SELECT l.item_id, 
                 SUM(oi.quantity) AS num_event_items
          FROM ".MENU_LISTS_TABLE." AS l, "
                .ORDERS_TABLE." AS o 
          WHERE o.order_id = ".ORDER_ITEMS_TABLE.".order_id) 
          AND l.menu_id = '$menu_id'
          AND o.event_id = '$event_id'
          AND o.complete = '1'
          AND ".ORDER_ITEMS_TABLE.".item_id = l.item_id 
          GROUP BY l.item_id 
          ORDER BY l.item_order";
?>

jdiver

Thanks very much for the reply. Unfortunately that still only returns the count for items that have been ordered for the event being checked. I need every item to have a value for each event, even if that value is zero. I can do it with a loop of the items, but that takes waaayyyy too long to execute.

jdiver

Finally figured it out 😃 :

		$sql_i = "SELECT l.item_id, SUM(oi.quantity) AS num_event_items 
				FROM " . MENU_LISTS_TABLE . " l, " . ORDERS_TABLE . " o 
					LEFT JOIN " . ORDER_ITEMS_TABLE . " oi ON o.order_id = oi.order_id AND l.item_id = oi.item_id 
				WHERE l.menu_id = '$menu_id' 
				AND o.event_id = '$event_id' 
				AND o.complete = '1' 
				GROUP BY l.item_id 
				ORDER BY l.item_order";