DBS DBS - 5 months ago 27
SQL Question

(MSaccess to MySQL) FROM subqueries with alias

I have a sql query in MS-Access which (when simplified to its basic structure) runs like this:

SELECT
tableA.id, tableB.id
FROM
(SELECT table.id FROM [table] WHERE (id mod 2 = 0)) AS tableA
INNER JOIN
(SELECT table.id FROM [table] WHERE (id mod 3 = 0)) AS tableB
ON tableA.id = tableB.id
;


That returns id's 6, 12 and so forth, for both A and B.

When adapting the script to MySQL it doesn't recognize the alias in the subqueries. I can't find a parallel in MySQL, what would it be?

Answer

table is not a good choice for a table name. Despite that name, your query works in MySQL 5.6 if you substitute backquotes for the square brackets.

SELECT 
  tableA.id, tableB.id
FROM
  (SELECT table.id FROM `table` WHERE (id mod 2 = 0)) AS tableA
  INNER JOIN
  (SELECT table.id FROM `table` WHERE (id mod 3 = 0)) AS tableB
  ON tableA.id = tableB.id
;

SQL Fiddle

That version of the query should also work in Access because Access SQL accepts either square brackets or backquotes as delimiters for object names.