Get the quantity of sold products on a specific day, keeping all products

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 1

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

Answer 2

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