Xiao Xinqi Xiao Xinqi - 2 months ago 20
MySQL Question

MySQL join three tables, sum of item join to main table

I have three MySQL tables:

For example


  • A Table is menu ID, name

  • B table is customer_order ID, order_date

  • C table is order_item ID, menu_item_id, customer_order_id, order_quantity



I try to output name, sum(order_quantity) in this month

Currently i have two separate query which working ok, but the second query is inside of foreach loop, which seem not so good.

First query which output all the menu items:

$results = $wpdb->get_results( "SELECT * FROM menu WHERE post_id = $pid ORDER BY sort_order ");


Second query will output total of each item sold on each month:

$total = $wpdb->get_col( "SELECT SUM(oi.order_item_quantity)
from order_item as oi
INNER JOIN customer_order as ho ON ho.ID = oi.order_id
WHERE oi.order_item_id = $subC->ID AND YEAR(ho.order_date) = $current_year AND MONTH(ho.order_date) = $current_month ");


I try to merge the two queries into one query, which has taken me whole day but still not able to solve it, can anyone give me some help please.

update

thanks Rene.

Select m.name, m.name as name, sum(oi.order_item_quantity) as sold_monthly from menu as m left join order_item as oi on oi.order_item_id = m.ID left join cusomter_order as co on co.ID = oi.order_id where m.post_id = 110 group by m.ID, m.name


this will output

name sold_monthly
Sushi Lunch Special NULL
Sushi Lunch 19
Sashimi Lunch 61
jason NULL
egg roll NULL


if i add
YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9


which i only get

name sold_monthly
Sushi Lunch 7
Sashimi Lunch 14


how can i keep sushi lunch special, jason, egg roll, the null item, when i add the
YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9.


here i try

(year(co.order_date) = 2016 and month(co.order_date) = 10) or sold_monthly is null


which give me a query error

update
thanks Rene again


it's working now

(year(co.order_date) = 2016 and month(co.order_date) = 10) or co.order_date is null

Answer

So you're trying to get a list per post_id limited by the selected month. The following query will yield that for the following sample data.

SELECT m.ID as ID, m.Name as Name, SUM(oi.order_quantity) as Quantity
FROM menu as m
LEFT JOIN order_item as oi ON oi.menu_item_id = m.ID 
LEFT JOIN customer_order as co ON co.ID = oi.customer_order_id
WHERE m.post_id = 0 AND YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9 OR co.order_date is NULL
GROUP BY m.ID,m.Name,m.sort_order
ORDER BY m.sort_order

Sample Data

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

DROP TABLE IF EXISTS `customer_order`;
CREATE TABLE `customer_order` (
  `ID` int(11) NOT NULL,
  `order_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;

TRUNCATE TABLE `customer_order`;
INSERT INTO `customer_order` (`ID`, `order_date`) VALUES
(1, '2016-09-06 00:00:00'),
(2, '2016-09-13 00:00:00'),
(3, '2016-08-09 00:00:00'),
(4, '2016-09-19 00:00:00');

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
  `ID` int(11) NOT NULL,
  `sort_order` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `Name` varchar(20) COLLATE utf8_german2_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;

TRUNCATE TABLE `menu`;
INSERT INTO `menu` (`ID`, `sort_order`, `post_id`, `Name`) VALUES
(2, 0, 0, 'Test 1'),
(4, 1, 0, 'Test 2'),
(5, 2, 0, 'Test 3');

DROP TABLE IF EXISTS `order_item`;
CREATE TABLE `order_item` (
  `ID` int(11) NOT NULL,
  `menu_item_id` int(11) NOT NULL,
  `customer_order_id` int(11) NOT NULL,
  `order_quantity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_german2_ci;

TRUNCATE TABLE `order_item`;
INSERT INTO `order_item` (`ID`, `menu_item_id`, `customer_order_id`, `order_quantity`) VALUES
(1, 2, 1, 1),
(2, 2, 2, 3),
(3, 4, 1, 1),
(4, 4, 2, 4),
(5, 2, 3, 3),
(6, 4, 3, 1),
(7, 2, 4, 4);


ALTER TABLE `customer_order`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `menu`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `idx_pid` (`post_id`);

ALTER TABLE `order_item`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `idx_coid` (`customer_order_id`),
  ADD KEY `idx_miid` (`menu_item_id`);


ALTER TABLE `customer_order`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE `menu`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
ALTER TABLE `order_item`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `order_item`
  ADD CONSTRAINT `CostomerOrderConstrain` FOREIGN KEY (`customer_order_id`) REFERENCES `customer_order` (`ID`),
  ADD CONSTRAINT `MenuItemConstrain` FOREIGN KEY (`menu_item_id`) REFERENCES `menu` (`ID`);

Good luck integrating the query, let me know if it worked.

Update: Updated sample data to reproduce the actual problem. Updated the Solution Query.