MySQL - How to add alias syntax to a subquery

Question

I am new to MySQL and still have problems with its syntax. I have this query:

SELECT a, b, c, d, e FROM table1 WHERE status = 'skipped' AND batchid IN (SELECT batchid FROM (SELECT distinct batchid, date_format(uploaddate, '%Y-%m-%d') FROM table1 WHERE uploaddate > '2011-01-26') AS t ) AS t; 

which gives me this error:

 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 'as t' at line 1 

The IN clause works:

 SELECT batchid FROM (SELECT distinct batchid, date_format(uploaddate, '%Y-%m-%d') FROM table1 WHERE uploaddate > '2011-01-26') AS t 

I think my problem relates to the second alias of the table. Can someone show me what I'm doing wrong?

Answer 1

You do not need an alias for the subqueries that appear in the WHERE .

Leave the final AS t (just before the semicolon), and everything should be fine.