[SOLVED] How to fill the empty record per group using mysql?

Issue

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?

Solution

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

Answered By – D-Shih

Answer Checked By – Willingham (BugsFixing Volunteer)