Get the quantity of sold products on a specific day, keeping all products 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 &quot;0 pears sold&quot;

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 &quot;Apple&quot;) | 2 | 2.50 |
|1 | 5 (it is &quot;Banana&quot;) | 3 | 3.50 |
|2 | 2 (it is &quot;Apple&quot;) | 1 | 2.00 |
|2 | 5 (it is &quot;Banana&quot;) | 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 &quot;0&quot;, 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 &quot;0 pears sold&quot;, because the query discards the products that have not been ordered in &quot;that date&quot;
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 &quot;that date&quot;
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 &quot;ProductOrders&quot; 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;
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&lt;&gt;fiddle here