Question 21610 in Mysql Question posted in
The official MySQL documentation can be found here.

return 0 after joining two tables in mysql

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
Tags:

2

Answers


  1. 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|
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search