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)
);

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:

But what i want is:

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?