With As Clause Not recognized MySQL-CodePudding

I am trying to use a with as clause in a query.

WITH NAME AS 
(
SELECT col1,
       col2
FROM TABLE1
)

SELECT col2, 
       col3
FROM TABLE2
WHERE col2 in (NAME.col1)

The query placed in the with as clause works on it's own but I get this error:

Unknown column NAME.'col1' in 'where clause'

I am more familiar with oracle so this may have caused some error by carrying something over.

Is the order of the clauses wrong and With As comes after where?

CodePudding user response：

I think you do not need the CTE.

SELECT col2, 
       col3
FROM TABLE2
WHERE col2 in (SELECT col1 FROM TABLE1)

should work

If you want to use CTE (for what reason ever), you have to select from the table

WITH NAME AS 
(
SELECT col1,
       col2
FROM TABLE1
)

SELECT col2, 
       col3
FROM TABLE2
WHERE col2 in (select col1 from name)