CODEWITHSUNDEEP
mysqlleft-joinwhere-clause
jose
jose

Reputation: 1562

LEFT JOIN and WHERE clause - list all records even those with a value of null

The goal is to display on a grid a list of all sellers with sales and values.

The list of sellers should always be displayed, even when they have no sales.

Something like:

seller | sales | value
 John      10      1000
 Marie     20      2000
 Jamie     15      1500
 Arnold     0         0
 Peter      0         0

For that the next query works fine:

 SELECT tsl.sellers, 
       COUNT(ts.sales) AS sales,
       COALESCE(SUM(ts.value),0) AS value
 FROM tab_sellers tsl
 LEFT JOIN tab_sales ts ON  tsl.id_seller = ts.seller
 GROUP BY tsl.seller

The problem is when I need to filter by date range.

 SELECT tsl.sellers, 
       COUNT(ts.sales) AS sales,
       COALESCE(SUM(ts.value),0) AS value
 FROM tab_sellers tsl
 LEFT JOIN tab_sales ts ON  tsl.id_seller = ts.seller
 WHERE ts.date_sale BETWEEN '2017-05-01' and '2017-07-04'
 GROUP BY tsl.seller

Considering that only John and Marie have sales in this date range, the result is:

seller | sales | value
 John       5       500
 Marie     10      1000

However, the intended result is:

seller | sales | value
 John       5       500
 Marie     10      1000
 Jamie      0         0
 Arnold     0         0
 Peter      0         0

I appreciated ideas to solve this.

Upvotes: 1

Views: 64

Answers (1)

chirag satapara
chirag satapara

Reputation: 1937

Place condition in And oprtation instead of where.

Try below query:

 SELECT tsl.sellers, 
       COUNT(ts.sales) AS sales,
       COALESCE(SUM(ts.value),0) AS value
 FROM tab_sellers tsl
 LEFT JOIN tab_sales ts ON  tsl.id_seller = ts.seller AND ts.date_sale BETWEEN '2017-05-01' and '2017-07-04'
 GROUP BY tsl.seller

Hope this may helpful to you.

Upvotes: 2

Related Questions