The equivalent of Common Table Expressions in MySQL

Question

I have a query which I was running in Microsoft SQL server and it worked just fine. It made use of CTEs because I joined three tables. The first two in an inner query then an outer one. However when I tried this on my web server it only has MySQL installed and I could not run the query. Is there any way to run a query with that structure? Here is a sample:

WITH CTE_DatesTable 
AS
(
SELECT CAST('20160401' as date) AS [date]
UNION ALL
SELECT DATE_ADD(dd, 1, [date])
FROM CTE_DatesTable
WHERE DATE_ADD(dd, 1, [date]) <= '20170331'
) Select A.[date],
(SELECT CASE DATEPART(WEEKDAY,A.[date])
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY' End) WkDay,
Case When Isnull(C.Hdate,'')<>'' then C.Descr
When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 Then 'AlterWeekoff'
When B.WkOff=0 Then 'Weekoff'Else 'WD' End As WorkDay,
Case When (DATEPART(WK,A.[Date])%2)=1 And B.WkOff=1 or Isnull(C.Hdate,'')<>'' Then 'Holiday'
When B.WkOff=0 Then 'Holiday' Else 'WeekDay' End As WorkDescr
From CTE_DatesTable A Left Join BPC_WDD1 B on DATEPART(dw,A.[Date])=B.WDay
Left Join BPC_HCL1 C on A.date=C.Hdate
OPTION (MAXRECURSION 0);

Answer 1

Onetime action: Build a table with lots of dates-- a la DatesTable. (This can be done with a cross join to help generate lots of numbers, then use + INTERVAL n DAY for turning into dates.)

Then LEFT JOIN (with a WHERE clause) from the DatesTable to the other table(s).

Note that you will need to rewrite most date operations because of different syntax.

With MariaDB, you can use a pseudo table like seq_1_to_1000 to get a bunch of numbers. Also, MariaDB has CTEs. (MySQL will be getting CTEs sometime in version 8.0.)