Adding Where Clause To Query Skews Result Set

I am attempting to include all data from my calendar table and only the data from my sales table that meets the where criteria. My issue is that the query I am executing ONLY returns the months that the WHERE person has sales.

How should this MySQL query be altered so that everything from LEFT table is returned?

 Create Table InvoiceDataCalendar (
  id INT,
  cal_month_year varchar(90)
  );

 Create Table Sales (
     salesperson varchar(50),
     sale_month_year varchar(90),
     sale_amount int
   );

 INSERT INTO Sales Values ('Bruce 1', 'Jan 18', 200);
 INSERT INTO Sales Values ('Bruce 1', 'Feb 19', 400);
  INSERT INTO Sales Values ('Jimmy 2', 'Jan 18', 200);
 INSERT INTO Sales Values ('Jimmy 2', 'Feb 19', 400);
 Insert Into InvoiceDataCalendar VALUES (1, 'Jan 18');
 Insert Into InvoiceDataCalendar VALUES  (2, 'Jan 19');
 Insert Into InvoiceDataCalendar VALUES  (3, 'Feb 18');
  Insert Into InvoiceDataCalendar VALUES  (4, 'Feb 19');
 Insert Into InvoiceDataCalendar VALUES  (5, 'Mar 18');
 Insert Into InvoiceDataCalendar  VALUES (6, 'Mar 19');
  Insert Into InvoiceDataCalendar VALUES  (7, 'Apr 18');
 Insert Into InvoiceDataCalendar VALUES  (8, 'Apr 19');
 Insert Into InvoiceDataCalendar VALUES  (9, 'May 18');
  Insert Into InvoiceDataCalendar VALUES  (10, 'May 19');
 Insert Into InvoiceDataCalendar VALUES  (11,'Jun 18');
 Insert Into InvoiceDataCalendar  VALUES (12, 'Jun 19');
  Insert Into InvoiceDataCalendar VALUES  (13, 'Jul 18');
 Insert Into InvoiceDataCalendar VALUES  (14, 'Jul 19');
 Insert Into InvoiceDataCalendar  VALUES (15,'Aug 18');
  Insert Into InvoiceDataCalendar VALUES (16, 'Aug 19');
 Insert Into InvoiceDataCalendar VALUES (17, 'Sep 18');
 Insert Into InvoiceDataCalendar VALUES (18, 'Sep 19');
  Insert Into InvoiceDataCalendar VALUES (19, 'Oct 18');
 Insert Into InvoiceDataCalendar VALUES (20, 'Oct 19');
 Insert Into InvoiceDataCalendar VALUES (21, 'Nov 18');
  Insert Into InvoiceDataCalendar VALUES (22, 'Nov 19');
 Insert Into InvoiceDataCalendar VALUES (23, 'Dec 18');
 Insert Into InvoiceDataCalendar VALUES (24, 'Dec 19');


 Select 
ic.cal_month_year
,s.salesperson
,SUM(s.sale_amount) As TotalSales
FROM InvoiceDataCalendar ic
LEFT JOIN Sales s
ON ic.cal_month_year = s.sale_month_year
WHERE s.salesperson = 'Bruce 1'
GROUP BY ic.cal_month_year, s.salesperson, ic.id
ORDER BY id ASC;

Answer

You should not use left joined table’s column in where condition because this way work as an inner join
instead add the condition to the ON clause

 Select  ic.cal_month_year
,s.salesperson
,SUM(s.sale_amount) As TotalSales
FROM InvoiceDataCalendar ic
LEFT JOIN Sales s ON ic.cal_month_year = s.sale_month_year 
    AND  s.salesperson = 'Bruce 1'
GROUP BY ic.cal_month_year, s.salesperson, ic.id
ORDER BY id ASC;

Advertisement

Answer