CREATE TABLE itemmaster (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE `order` (
orderid INT PRIMARY KEY,
date DATE
);
CREATE TABLE orderdetails (
id INT PRIMARY KEY,
orderid INT,
itemid INT,
quantity INT,
FOREIGN KEY (orderid) REFERENCES `order` (orderid),
FOREIGN KEY (itemid) REFERENCES itemmaster (id)
);
INSERT INTO itemmaster (id, name) VALUES
(1, 'Pizza'),
(2, 'Burger'),
(3, 'Pepsi');
INSERT INTO `order` (orderid, date) VALUES
(1, '2011-01-01'),
(2, '2011-01-02'),
(3, '2011-01-03');
INSERT INTO orderdetails (id, orderid, itemid, quantity) VALUES
(1, 1, 1, 10),
(2, 1, 2, 20),
(3, 2, 1, 10);
SELECT
`im`.`id`,
`im`.`name`,
SUM(
CASE
WHEN `o`.`date` = '2011-01-01' THEN `od`.`quantity`
ELSE 0
END
) AS `total_qty`
FROM `itemmaster` AS `im`
LEFT JOIN `orderdetails` AS `od`
ON `im`.`id` = `od`.`itemid`
LEFT JOIN `order` AS `o`
ON `od`.`orderid` = `o`.`orderid`
GROUP BY `im`.`id`
ORDER BY `im`.`name`;
Telegram: DB developers channel