return 0 after joining two tables in mysql-CodePudding

im trying to display all the items sold and not sold within a period of time

im unable to display on the join tables all the items that have previously arrived if there were none sold.

select arrivals.description,
    COALESCE(sum(orders.quantity ), 0) as quantity
    from arrivals
    left join
    orders
    on  arrivals.description = orders.description
    where orders.date between '2022-11-01' and '2022-12-20'
    group by orders.description`ARRIVALS

the problem is that by referencig orders.date doesnt display descriptions where nothing was sold within that period, it works if i use arrivals.date but i want to display the dates from orders.

ORDERS

    | id|date      | description       | quantity   |    
    |---|----------|-------------------|------------|
    |7| 2022-11-27 | nike 500 black 70 |1|
    |1| 2022-11-24 | nike 500 black 70 |1|
    |2| 2022-11-24 | nike 500 black 60 |1|
    |6| 2022-11-28 | adidas 1000 white 90 |1|
    |5| 2022-11-27 | adidas 1000 white 90 |1|
    |4| 2022-10-31 | adidas 1000 white 90 |1|
    |3| 2022-10-31 | adidas 1000 white 80 |1|

--------------------------------------------------------
ARRIVALS

| id |date        | description          |  quantity    |    
|----|------------|----------------------|-------------|
|1   |2022-10-30  | nike 500 black 50    | 2  |
|2   | 2022-10-30 | nike 500 black 60    | 3  |
|3   | 2022-10-30 | nike 500 black 70    | 4  |
|4   | 2022-10-29 | adidas 1000 white 80 | 2  |
|5   | 2022-10-29 | adidas 1000 white 90 | 3  |
|6   | 2022-10-29 | adidas 1000 white 110| 2  |

i only get this result: | description | quantity |
|----------------------|-------------| | nike 500 black 60 |1 | | nike 500 black 70 |2 | | adidas 1000 white 90 |2 |

CodePudding user response：

Can you try this?

select arrivals.description,
       COALESCE(sum(orders.quantity ), 0) as quantity
from arrivals
left join orders on arrivals.description = orders.description
     and orders.date between '2022-11-01' and '2022-12-20'
group by arrivals.description

This will return all the descriptions in the arrivals table, with a quantity of 0 for the ones that have no matching records in the orders table within the specified date range.