MySQL соединяет три таблицы, сумма элементов присоединяется к главной таблице

У меня есть три таблицы MySQL:

Например

  • Таблица-это идентификатор меню, имя

  • Таблица B-идентификатор customer_order, order_date

  • Таблица с order_item идентификатор, menu_item_id, customer_order_id, order_quantity

Я пытаюсь вывести имя, сумму (order_quantity) в этом месяце

В настоящее время у меня есть два отдельных запроса, которые работают нормально, но второй запрос находится внутри цикла foreach, который кажется не так хороший.

Первый запрос, который выводит все пункты меню: 

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

Второй запрос выведет итог каждого проданного товара за каждый месяц: 

$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 ");
Я пытаюсь объединить два запроса в один запрос, который занял у меня целый день, но все еще не в состоянии решить его, может ли кто-нибудь помочь мне, пожалуйста.

Обновить

Спасибо Рене.

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

Это выведет 

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

Если я добавлю YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9

Который я получаю только 

 name                   sold_monthly    
Sushi Lunch             7
Sashimi Lunch           14

Как могу ли я оставить суши-ланч особенным, Джейсон, яичный ролл, нулевой элемент, когда я добавляю YEAR(co.order_date) = 2016 AND MONTH(co.order_date) = 9.

Вот я и пытаюсь 

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

Которые дают мне ошибку запроса

Обновление еще раз спасибо Рене

Теперь это работает 

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

Наконец, решите его, у меня есть небольшая ошибка, когда я изменяю business_id, который может не поймать результат, который я хочу, поэтому я добавляю к нему подзапрос. 

Select m.*, p.sold_monthly from menu as m left join ( SELECT SUM(oi.order_item_quantity) as sold_monthly, oi.order_item_id as ID, oi.order_item_name from order_item as oi LEFT JOIN cusomter_order as ho ON ho.ID = oi.order_id WHERE ho.business_id = $pid AND (year(ho.order_date) = $current_year and month(ho.order_date) = $current_month) OR ho.order_date is NULL GROUP by oi.order_item_id )p on p.ID = m.ID where m.post_id = $pid
1 2
sql mysql jointable

1 ответ:

Таким образом, вы пытаетесь получить список на post_id, ограниченный выбранным месяцем. Следующий запрос даст это для следующих образцов данных.

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

Примерные Данные 

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

Удачи в интеграции запроса, дайте мне знать, если это сработает.

Обновление: обновленные образцы данных для воспроизведения фактической проблемы. Обновлен запрос решения.

1