With As Clause Not recognized MySQL

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’

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

>Solution :

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)