mysql - Multiple UNION query doesn't work

Question

The multiple UNION query:

SELECT [Ordine numero] AS ordine, [data ordine] AS data, comm AS commessa
FROM [archivio globale]
WHERE [ordine numero] IS NOT NULL

UNION

SELECT [numero ordine cliente] AS ordine, [data ordine cliente] AS data, numero AS commessa
FROM [ricambi]
WHERE [numero ordine cliente] IS NOT NULL

UNION

SELECT [numero ordine cliente] AS  ordine, [data ordine cliente] AS data, numero AS commessa
FROM [trasferte]
WHERE [numero ordine cliente] IS NOT NULL

ORDER BY [ordine];

It doesn't work: I get the error message:

[mysql][odbc 5.1 Driver][mysqld-5.5.14]You have an error in your SQL syntax ...

While the single UNION query works properly (without the second UNION statement)? What to do?

Answer 1

You have tagged your question as MySQL and you are using square brackets []. As far as I know, square brackets are not valid for MySQL and are only valid for Microsoft products (SQL Server/MS Access). So if you need to enclose table/column name use backticks `.

From the documentation:

The identifier quote character is the backtick (“`”):

So I think your query should be:

SELECT `Ordine numero` AS ordine, `data ordine` AS data, comm AS commessa
FROM `archivio globale`
WHERE `ordine numero` IS NOT NULL

UNION ALL

SELECT `numero ordine cliente` AS ordine, `data ordine cliente` AS data, numero AS commessa
FROM `ricambi`
WHERE `numero ordine cliente` IS NOT NULL

UNION ALL

SELECT `numero ordine cliente` AS  ordine, `data ordine cliente` AS data, numero AS commessa
FROM `trasferte`
WHERE `numero ordine cliente` IS NOT NULL

ORDER BY `ordine`;

Edit, if you are using MS Access then you will need to use the square brackets:

SELECT *
FROM
(
  SELECT [Ordine numero] AS ordine, [data ordine] AS data, comm AS commessa
  FROM [archivio globale]
  WHERE [ordine numero] IS NOT NULL

  UNION ALL

  SELECT [numero ordine cliente] AS ordine, [data ordine cliente] AS data, numero AS commessa
  FROM [ricambi]
  WHERE [numero ordine cliente] IS NOT NULL

  UNION ALL

  SELECT [numero ordine cliente] AS  ordine, [data ordine cliente] AS data, numero AS commessa
  FROM [trasferte]
  WHERE [numero ordine cliente] IS NOT NULL
) x
ORDER BY [ordine];