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