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;