return 0 after joining two tables in mysql

Question

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

score 0 · Answer 1 · answered Dec 25 '22 at 02:36

0

The issue is due to orders.date between '2022-11-01' and '2022-12-20' is in the where clause. If you want to include everything in arrivals and only orders with orders.date between '2022-11-01' and '2022-12-20', you can move it to select as below:

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

Result:

description          |quantity|
---------------------+--------+
nike 500 black 50    |       0|
nike 500 black 60    |       1|
nike 500 black 70    |       2|
adidas 1000 white 80 |       0|
adidas 1000 white 90 |       2|
adidas 1000 white 110|       0|

answered Dec 25 '22 at 02:36

JHH

1,331
1
2
9

thanks I cant take advantage of this approach as i can display sells within 15, 30 , 45 days – Jorgefilemon Dec 27 '22 at 18:02
select a.description, sum(if(o.date between '2022-11-01' and '2022-11-16', o.quantity, 0)) as '15 days', sum(if(o.date between '2022-11-01' and '2022-12-01', o.quantity, 0)) as '30 days', sum(if(o.date between '2022-11-01' and '2022-12-01', o.quantity, 0)) as '45 days' from arrivals a left join orders o on a.description = o.description group by a.description – Jorgefilemon Dec 27 '22 at 18:02

score 0 · Accepted Answer · answered Dec 25 '22 at 16:30

Move the condition on order date from the where clause to the join condition:

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

The reason this works is that when you put a condition on an outer joined table in the where clause you effectively change the join from an outer join to an inner join.

Other cosmetic changes were made to the query to improve readability such as standard formatting, use of aliases, etc.

didnt know i could use between dates without where. – Jorgefilemon Dec 26 '22 at 19:07 — Jorgefilemon, Dec 26 '22 at 19:07

return 0 after joining two tables in mysql

2 Answers2