query not working only when inside WITH ... AS statement-CodePudding

I have a pretty difficult query to ask so I want to use the WITH ... AS syntax to break it down to smaller steps. I am trying to build a dummy query to ses how it works, which basically selects the stations that belong to a certain company and just selects everything again.

WITH company_stations AS (
    SELECT * FROM Station WHERE Station.stationProvider = 'olympia_odos'
) SELECT *  FROM  company_stations;

the inside query works fine on it's own

SELECT * FROM Station WHERE Station.stationProvider = 'olympia_odos'

But when I use it Inside the with as statement it gives me the very helpful error message

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'company_stations AS (SELECT stationName FROM Station WHERE Station.stationProvid' at line 1

I tried searching for the ERROR 1064 (42000) but it seems that everyone that faced this error was in the process of building, populating or accessing a DB. I have done all these things, but the specific query seems to be problematic.

Also I tried all the usual suspects such as ' or " or `, ( or no ( etc.

CodePudding user response：

CTE expressions is not supported before MySQL 8.0 You can use sub-queries instead:

SELECT * FROM (
    SELECT * FROM Station WHERE Station.stationProvider = 'olympia_odos'
) AS company_stations;