2

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?

Taryn
  • 242,637
  • 56
  • 362
  • 405
user1732326
  • 29
  • 1
  • 1
  • 2

3 Answers3

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];
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • I copied the above query (I work with ACCESS) but I got the same error 1064 – user1732326 Oct 09 '12 at 16:54
  • @user1732326 what database are you working in, you tagged the question as MySQL, you but now you are saying Access? The syntax can be different for each product. Please clarify. – Taryn Oct 09 '12 at 16:55
  • square brackets or backticks have the same result; One UNION ALL statement works, two NOT – user1732326 Oct 09 '12 at 16:56
  • My database is in MYSQL, tables or views are linked in ACCESS – user1732326 Oct 09 '12 at 16:57
  • @user1732326 where are you running this query? In MySQL or in MS Access? – Taryn Oct 09 '12 at 16:58
  • I execute the query in ACCESS – user1732326 Oct 09 '12 at 16:59
  • I tried to execute the query directly in MYSQL workbench, it works fine; but the same syntax in ACCESS doesn't work – user1732326 Oct 09 '12 at 17:04
  • Which one are you attempting to query in MySQL and then which one in MS Access? The syntax is different for both of those systems. You cannot interchange the sql syntax between MySQL and Access. – Taryn Oct 09 '12 at 17:05
  • I executed the SAME query inside MYSQL workbench, and it works; the IDENTICAL query in ACCESS doesn't work; the single UNION query, with the same syntax, works in ACCESS; when adding the second UNION it doesn't work, while in MYSQL behavior yes – user1732326 Oct 10 '12 at 17:06
  • @user1732326 when running this in Access do you get an error message? – Taryn Oct 10 '12 at 17:11
  • The query in ACCESS with BRACKETS or with BACKTICK give the same result; it seems that the syntax difference is not important, you can use brackets or ticks either – user1732326 Oct 10 '12 at 17:12
1

It's a MyODBC bug see offical entry here. As of today, it's still open...

[EDIT] I found a simple solution: myODBC will only understand queries with ONE UNION (or UNION ALL). So you have to wrap the right part of the UNION with (). Plus, you'll need to end each query with ;, like this:

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
);

And of course, you must apply the same method to this part, too. For instance, if you needed one more UNION ALL in you query:

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;

UNION ALL

SELECT [numero ordine cliente] AS  ordine, [data ordine cliente] AS data, numero AS commessa
FROM [xxx]
WHERE [numero ordine cliente] IS NOT NULL
));
Pat
  • 411
  • 5
  • 4
  • What you could do is create each paire of unioned selects in seperate quieries (in Access), and then union them in other quieres. It'll work just as long as you don't have more than one union per query. – Pat Feb 21 '13 at 13:56
0

In my case MySQL linked tables to MDB gave me the same error. After multiple hours of search and try , my solution was to add ''& in front of the first column, like this: SELECT ''&COUNT(*) AS MYNR,...

this way i managed to use UNION ALL 4x.

Hope it helps someone

The link to the bug

Molnar Istvan
  • 77
  • 5