MYSQL left join include not existing rows-CodePudding

I currently have created the following extremely simplified mysql table that mimics the current tables i have.

promotion table:

CREATE TABLE promotion (
  promotion Varchar NOT NULL,
  PRIMARY KEY (promotion)
);

sale_id
10%
20%
30%

and sale table:

CREATE TABLE sale (
  sale_id int NOT NULL AUTO_INCREMENT,
  promotion Varchar,
  date Date NOT NULL,
  amount int,
  PRIMARY KEY (sale_id)
);

sale_id	promotion	date	amount
1	null	2022-1-1	100
2	10%	2022-1-1	100
3	10%	2022-1-1	100
4	20%	2022-1-1	100
5	null	2022-1-2	100
6	10%	2022-1-2	100
7	30%	2022-1-2	100
8	30%	2022-1-2	100

where promotion_id is a foreign key to the promotion table. So the relationship is that every sale can have either no promotion (null) or a promotion (int id value of the promotion's primary key as a foreign key).

I'm trying to display the data group by month and then promotion, wherein every month has a bunch of grouped sale amount totals grouped by promotion.

So far I have this query:

select
    Month(date),
    ifnull(promotion,'No Promo'),
    sum(amount)
from sale
    left join promotion p on p.promotion = sale.promotion
group by Month(date), sale.promotion

and this gives me the following results:

month	promotion	amount
1	null	100
1	10%	200
1	20%	100
2	null	100
2	10%	100
2	30%	200

But what i want is:

month	promotion	amount
1	null	100
1	10%	200
1	20%	100
1	30%	0
2	null	100
2	10%	100
2	20%	0
2	30%	200

Doesnt doing a right join mean including every promotion from the list of promotions in the promotion table? regardless if there was a sale with that promotion in that month? Can anyone give me a hint in the right direction of why my right join is not working?

CodePudding user response：

You need to inverse the order of your tables in the join. (A right join would also work, but is not supported in all versions)

select
    Month(date),
    ifnull(promotion,'No Promo'),
    sum(amount)
from promotion p
    left join sale on p.promotion = sale.promotion
group by Month(date), ifnull(promotion,'No Promo');

CodePudding user response：

I've slightly modified your query and your table definitions and its working fine in dbFiddle.

CREATE TABLE promotion (
  promotion Varchar(4) NOT NULL
  PRIMARY KEY
);

✓

INSERT INTO promotion VALUES ('10%'),('20%'),('30%');

✓

CREATE TABLE sale (
  sale_id int NOT NULL PRIMARY KEY  AUTO_INCREMENT,
  promotion Varchar(4),
  date_ Date NOT NULL,
  amount int
);

✓

insert into sale (promotion, date_, amount) values
(null,'2022-1-1',100),
('10%','2022-1-1',100),
('20%','2022-1-1',100),
('30%','2022-1-1',100),
(null,'2022-1-2',100),
('10%','2022-1-2',110),
('20%','2022-1-2',120),
('30%','2022-1-2',130),
(null,'2022-2-1',75),
('10%','2022-2-1',75),
('20%','2022-2-1',75),
('30%','2022-2-1',75),
(null,'2022-2-2',75),
('10%','2022-2-2',75),
('20%','2022-2-2',75),
('30%','2022-2-2',75);

✓

select
    Month(date_) month_,
    ifnull(p.promotion,'No Promo') Promotion,
    sum(amount) Total
from sale
    left join promotion p on p.promotion = sale.promotion
group by Month(date_), ifnull(p.promotion,'No Promo');

month_ | Promotion | Total
-----: | :-------- | ----:
     1 | No Promo  |   200
     1 | 10%       |   210
     1 | 20%       |   220
     1 | 30%       |   230
     2 | No Promo  |   150
     2 | 10%       |   150
     2 | 20%       |   150
     2 | 30%       |   150

db<>fiddle here