Question :
I’m building a database for daily sales report, for example:
2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12
0 pears sold on 2021-02-12
Notice the “0 pears sold”
I have these tables:
Products
id (PK) | name | deleted_at |
---|---|---|
2 | Apple | null |
5 | Banana | null |
6 | Pear | null |
Orders
id (PK) | sale_date (DATE) |
---|---|
1 | 2021-02-12 |
2 | 2021-02-12 |
ProductOrders
|order_id (FK)|product_id (FK)|quantity|price|
|1 | 2 (it is “Apple”) | 2 | 2.50 |
|1 | 5 (it is “Banana”) | 3 | 3.50 |
|2 | 2 (it is “Apple”) | 1 | 2.00 |
|2 | 5 (it is “Banana”) | 3 | 3.50 |
I would like to get the products sold on 2021-02-12 grouped by price, keeping products that have not been sold so that I can write “0”, as in the example.
I tried this query:
SELECT
p.id AS prod_id,
p.name AS prod_name,
sum(po.quantity) AS quantity_sold,
po.price AS price
FROM products p
JOIN ProductOrders AS po ON p.id = po.product_id
JOIN Orders AS o ON po.order_id = o.id
WHERE o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price
With this query I get:
2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12
Without the “0 pears sold”, because the query discards the products that have not been ordered in “that date”
How can I to edit this query to get all products, also if they has not been sold in the date? Because I need to write 0 for the products has not been sold in “that date”
Thank you in advance and sorry for my bad english
EDIT
I forgot to say that I tried to use LEFT JOIN, but the problem is that in this way, it will keep all orders
SELECT
p.id AS prod_id,
p.name AS prod_name,
sum(po.quantity) AS quantity_sold,
po.price AS price
FROM products p
LEFT JOIN ProductOrders AS po ON p.id = po.product_id
LEFT JOIN Orders AS o ON po.order_id = o.id AND o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price
This query will keep all “ProductOrders” so it will returns many many rows, so I can’t use this solution
SOLUTION
Thanks to the user nbk for this solution (I just added the WHERE clause to discard deleted products that have not orders on “THE DATE”):
SELECT
p.id AS prod_id,
p.name AS prod_name,
p.deleted_at,
sum(po.quantity) AS quantity_sold,
po.price as price
FROM products p
LEFT JOIN (ProductOrders AS po
INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id = o.id
) ON p.id = po.product_id
WHERE (p.deleted_at IS NULL OR (p.deleted_at IS NOT NULL AND po.product_id IS NOT NULL))
GROUP BY p.id, p.name, p.deleted_at, po.price
ORDER BY p.name, po.price;
Answer :
If you do a ddl you get quicker a good result.
you have to LEFT JOIN
the tables to get the Rows with no partners.
Also add the where clause directly to the ON Clause or else you loose the gained rows.
CREATE TABLE products (
`id` INTEGER Primary Key,
`name` VARCHAR(6),
`deleted_at` VARCHAR(4)
);
INSERT INTO products
(`id`, `name`, `deleted_at`)
VALUES
('2', 'Apple', 'null'),
('5', 'Banana', 'null'),
('6', 'Pear', 'null');
CREATE TABLE Orders (
`id` INTEGER PRIMARY KEY,
`sale_date` DATE
);
INSERT INTO Orders
(`id`, `sale_date`)
VALUES
('1', '2021-02-12'),
('2', '2021-02-12');
CREATE TABLE ProductOrders (
`order_id` INTEGER,
`product_id` VARCHAR(18),
`quantity` INTEGER,
`price` DECIMAL(10,2)
);
INSERT INTO ProductOrders
(`order_id`, `product_id`, `quantity`, `price`)
VALUES
('1', '2', '2', '2.50'),
('1', '5', '3', '3.50'),
('2', '2', '1', '2.00'),
('2', '5', '3', '3.50');
SELECT p.id AS prod_id, p.name AS prod_name, sum(po.quantity) AS quantity_sold FROM products p LEFT JOIN (ProductOrders AS po INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id = o.id) ON p.id = po.product_id GROUP BY p.id, p.name,po.price ORDER BY p.name;
prod_id | prod_name | quantity_sold ------: | :-------- | ------------: 2 | Apple | 1 2 | Apple | 2 5 | Banana | 6 6 | Pear | null
SELECT p.id AS prod_id, p.name AS prod_name, sum(po.quantity) AS quantity_sold FROM products p LEFT JOIN (ProductOrders AS po INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-13') AS o ON po.order_id = o.id) ON p.id = po.product_id GROUP BY p.id, p.name,po.price ORDER BY p.name;
prod_id | prod_name | quantity_sold ------: | :-------- | ------------: 2 | Apple | null 5 | Banana | null 6 | Pear | null
db<>fiddle here
When you don’t specify the type of JOIN
clause, by default MySQL assume you mean INNER JOIN
which only returns records that match the predicate in your JOIN
clause. Rather in your case you want all records from your products
table so you should use an OUTER JOIN
, specifically a LEFT OUTER JOIN
, to return every record from the table to the left of the JOIN
clauses, in this case the products
table. You can actually use the shorthand notation of LEFT JOIN
to mean the same thing. Here’s an example with your query:
SELECT
p.id AS prod_id,
p.name AS prod_name,
sum(coalesce(po.quantity, 0)) AS quantity_sold,
po.price AS price
FROM products p
LEFT JOIN ProductOrders AS po ON p.id = po.product_id
LEFT JOIN Orders AS o ON po.order_id = o.id
WHERE o.sale_date = THE DATE
OR o.sale_date IS NULL
GROUP BY p.id, p.name, po.price
ORDER BY p.name, po.price
Notice I also added OR o.sale_date IS NULL
to your WHERE
clause to ensure we don’t filter out the rows that don’t match (i.e. the products that weren’t sold on that date). When using an OUTER JOIN
, the tables on the right side of the JOIN
clause that don’t have matching rows will have NULL
values for their fields. We also then need to check if the po.quantity
is NULL
in the SUM()
function as well so we can set it to 0 (our default value for when that product
wasn’t sold on that date).
Alternatively you can accomplish the same results with this query that leverages a UNION ALL
with your original query in a CTE as well:
WITH CTE_ProductSales AS
(
SELECT
p.id AS prod_id,
p.name AS prod_name,
sum(po.quantity) AS quantity_sold,
po.price AS price
FROM products p
JOIN ProductOrders AS po ON p.id = po.product_id
JOIN Orders AS o ON po.order_id = o.id
WHERE o.sale_date = THE DATE
GROUP BY p.id, p.name, po.price
),
CTE_ProductSales_AllProducts AS
(
-- Products that were sold
SELECT prod_id, prod_name, quantity_sold, price
FROM CTE_ProductSales
UNION ALL
-- Products that weren't sold
SELECT id AS prod_id, name as prod_name, 0 AS quantity_sold, 0 as price
FROM products P
LEFT JOIN CTE_ProductSales PS
ON P.prod_id = PS.prod_id
WHERE PS.prod_id IS NULL -- Products that weren't sold on the date in the CTE
)
-- Final select of products sold with products that weren't sold
SELECT prod_id, prod_name, quantity_sold, price
FROM CTE_ProductSales_AllProducts
ORDER BY prod_name, price
And finally here’s a 3rd way you can accomplish this if you’re running a version of MySQL that doesn’t have CTEs:
-- Products sold on THE DATE
SELECT
p.id AS prod_id,
p.name AS prod_name,
sum(po.quantity) AS quantity_sold,
po.price AS price
FROM products p
JOIN ProductOrders AS po ON p.id = po.product_id
JOIN Orders AS o ON po.order_id = o.id
WHERE o.sale_date = THE DATE
UNION ALL
-- Products that weren't sold on THE DATE
SELECT DISTINCT
p.id AS prod_id,
p.name AS prod_name,
0 AS quantity_sold,
0 AS price
FROM products p
LEFT JOIN ProductOrders AS po ON p.id = po.product_id
LEFT JOIN Orders AS o ON po.order_id = o.id
WHERE o.sale_date = THE DATE
AND po.product_id IS NULL