CODEWITHSUNDEEP
mysqldatejoin
InsOp
InsOp

Reputation: 2699

Artificially duplicate results in mysql

I have a table like so

+---------+
| weekday |
+---------+
|    2    |
|    5    |
|    3    |
+---------+

Now I want to have a resultset in which I see the dates of this and the upcoming week like so:

+------------+---------+
|    date    | weekday |
+------------+---------+
| 2019-12-18 |    2    |
| 2019-12-21 |    5    |
| 2019-12-19 |    3    |
| 2019-12-25 |    2    |
| 2019-12-28 |    5    |
| 2019-12-26 |    3    |
+------------+---------+

So far I have this query

SELECT 
    CURDATE() + INTERVAL w.weekday - WEEKDAY(CURDATE()) DAY AS thisWeek,
    CURDATE() + INTERVAL w.weekday + 7 - WEEKDAY(CURDATE()) DAY AS nextWeek,
    dw.weekday
FROM
    weekdays AS w

Which gives me this result

+------------+------------+---------+
|  thisWeek  |  nextWeek  | weekday |
+------------+------------+---------+
| 2019-12-18 | 2019-12-25 |    2    |
| 2019-12-21 | 2019-12-28 |    5    |
| 2019-12-19 | 2019-12-26 |    3    |
+------------+------------+---------+

How would I have to need to proceed to get the former resultset?

Upvotes: 0

Views: 35

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You could try using UNION 

select  thisWeek date, weekday
from  (

  SELECT 
      CURDATE() + INTERVAL w.weekday - WEEKDAY(CURDATE()) DAY AS thisWeek,
      CURDATE() + INTERVAL w.weekday + 7 - WEEKDAY(CURDATE()) DAY AS nextWeek,
      dw.weekday
  FROM   weekdays AS w

) t1 
union all  
select  nextWeek date, weekday 
from  (

  SELECT 
      CURDATE() + INTERVAL w.weekday - WEEKDAY(CURDATE()) DAY AS thisWeek,
      CURDATE() + INTERVAL w.weekday + 7 - WEEKDAY(CURDATE()) DAY AS nextWeek,
      dw.weekday
  FROM   weekdays AS w
) t2

or for avoid subquery you could create a view 

create view my_view as  
SELECT 
      CURDATE() + INTERVAL w.weekday - WEEKDAY(CURDATE()) DAY AS thisWeek,
      CURDATE() + INTERVAL w.weekday + 7 - WEEKDAY(CURDATE()) DAY AS nextWeek,
      dw.weekday
  FROM   weekdays

then the query is 

select  thisWeek date, weekday
from my_view 
union all  
select  nextWeek date, weekday 
from  my_view

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Use UNION ALL.

SELECT 
    CURDATE() + INTERVAL w.weekday - WEEKDAY(CURDATE()) DAY AS date
    dw.weekday
FROM
    weekdays AS w
UNION ALL
SELECT 
    CURDATE() + INTERVAL w.weekday + 7 - WEEKDAY(CURDATE()) DAY AS date
    dw.weekday
FROM
    weekdays AS w;

Upvotes: 2

Related Questions