I am trying to handle the sql query in order to get what I want.

Below is the schema of the table.

CREATE TABLE MY_LOG (
    RANKING      VARCHAR(20)   
  , DAYOFWEEK      VARCHAR(10) 
  , MENU     VARCHAR(10)   
)

I have inserted some values and it looks like as below.

Ranking   DAYOFWEEK   MENU
1         MONDAY      PIZZA 
2         MONDAY      ICE CREAM
3         MONDAY      CHICKEN
4         MONDAY      RICE
5         MONDAY      BREAD
1         TUESDAY      PIZZA 
2         TUESDAY      ICE CREAM
3         TUESDAY      CHICKEN
4         TUESDAY      RICE
1         WEDNESDAY      PIZZA 
2         WEDNESDAY      ICE CREAM
3         WEDNESDAY      CHICKEN

As you can see, for each day of week, the ranking is shown with its menu. However, for Tuesday and Wednesday they have only four and three records. So I would like to insert the blank record as shown below.

Ranking   DAYOFWEEK   MENU
1         MONDAY      PIZZA 
2         MONDAY      ICE CREAM
3         MONDAY      CHICKEN
4         MONDAY      RICE
5         MONDAY      BREAD
1         TUESDAY      PIZZA 
2         TUESDAY      ICE CREAM
3         TUESDAY      CHICKEN
4         TUESDAY      RICE
5         -            -
1         WEDNESDAY      PIZZA 
2         WEDNESDAY      ICE CREAM
3         WEDNESDAY      CHICKEN
4         -              -
5         -              -

I have tried to resolve this issue but failed. How to achieve this?

CodePudding user response：

You can try to use OUTER JOIN with a subquery which does CORSS JOIN get a result RANKING & DAYOFWEEK Cartesian product

Query #1

SELECT t1.RANKING,
       t2.DAYOFWEEK,
       t2.MENU
FROM (
  SELECT DISTINCT t1.DAYOFWEEK,t2.RANKING
  FROM MY_LOG t1
  CROSS JOIN MY_LOG t2
) t1 LEFT JOIN  MY_LOG t2
ON t1.RANKING = t2.RANKING 
AND t1.DAYOFWEEK = t2.DAYOFWEEK
ORDER BY t1.DAYOFWEEK,t1.RANKING;

View on DB Fiddle

CodePudding user response：

when you want the other days of the week included too:

with recursive weekdays as (
   select dayname(curdate()) dn, curdate() as d 
   
   union all 
   
   select dayname(d 1),d 1 
   from weekdays 
   where d 1<date_add(curdate(), interval 7 DAY)
),
oneTofive as (
   select 1 as n union select 2 union select 3 union select 4 union select 5)
select 
   COALESCE(MY_LOG.RANKING, oneTofive.n) as Ranking,
   weekdays.dn as Weekday,
   MY_LOG.Menu
   
from weekdays
cross join oneTofive 
left join MY_LOG ON MY_LOG.DAYOFWEEK = weekdays.dn AND oneTofive.n=MY_LOG.RANKING
order by 
   weekdays.d, 
   COALESCE(MY_LOG.RANKING, oneTofive.n);

output: