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`,
COALESCE(SUM(`od_filtered`.`quantity`), 0) `total_qty`
FROM `itemmaster` `im`
LEFT JOIN (
SELECT `od`.*
FROM `orderdetails` `od`
JOIN `order` `o` ON `o`.`orderid` = `od`.`orderid`
WHERE `o`.`date` = '2011-01-01'
) `od_filtered` ON `im`.`id` = `od_filtered`.`itemid`
GROUP BY `im`.`id`
ORDER BY `im`.`name`;
Telegram: DB developers channel