Union query limit?

Is there a limit to the number of UNION queries I can run?  I'm fine at 2 but with 3 or more, I get this error:

"ODBC -- call failed.
[MySQL][ODBC 3.51 Driver][mysqld-5.0.51a-log] You have an error in your SQL syntax; ..."

I am using MySQL as my backend.  I can run the queries with 3 or more UNIONS in the mysql query browser.  It just fails with Access.  The number of records is small (6 total rows returned with 3 unions).


mock5cAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Try the Debug.Print  <Your SQL String> ... and see if it fits.  From the Immediate window, you can copy and paste into word or notepad, etc.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No specific limit per se.  I have one union query with 22 unions ... runs with no issue.  No clue what would be causing your issue.

mx
0
 
LambertHeenanCommented:
Please post the SQL that gives the error.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
mock5cAuthor Commented:
DatabaseMX has answered my specific question which is there is no union limit.  Clearly my problem must be SQL syntax.  The following example query would fail.

select "a" from T
UNION
select "b" from T
UNION
select "c" from T

0
 
mock5cAuthor Commented:
I'm going to increase points so I can get a solution to this problem.  DatabaseMX has already earned 50 points for answering there is no UNION limit.

My problem comes from my query.

If I typed:

select "a" from Table1
UNION
select "b" from Table1

I will get:
a
b

as the result

But if I throw another UNION in there,

select "a" from Table1
UNION
select "b" from Table1
UNION
select "c" from Table1

I get that ODBC error message. What is wrong with my syntax?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here is the general format for a UNION query:

SELECT Table1.* FROM Table1
UNION SELECT Table2.* FROM Table2
UNION SELECT Table3.* FROM Table3
UNION SELECT Table4.* FROM Table4

and so on.

Note there are no double quotes ... and each table is a different table.

Also, the number of columns must be the same for each table (or query) data types for each column - in order - must match.  The names can be different.

mx
0
 
mock5cAuthor Commented:
I was just giving an example and the quotes are just for selecting a bogus piece of data.  All of the individual tables will come from the same query with a difference in the select clause for each of them.  The difference in the select clause is a name of a column (that's why I'm using quotes).  The datatypes and number of items in the select are the same.

I'm basically taking columns and transposing them into a single column and union'ing those queries together so all of the results are put in a single long vertical listbox instead of having a super wide result.

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... I don't know why you would be getting the ODBC error message.

Did you post this in the mySQL zone also


mx
0
 
mock5cAuthor Commented:
Is there a way to print all of my query to the screen?  If I could do that, then I could examine my query more closely or paste it hear.  I use MsgBox but there's a character limit.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, you could use Debug.Print  <some variable> ... and see the results in the Immediate Window.  I believe there is a limit there also, but more than the Message Box.

By 'print all ...' ... not sure what you mean?  results? SQL?


mx
0
 
mock5cAuthor Commented:
print the query string.  It's very large after many unions.
0
 
mock5cAuthor Commented:
has anyone tried the following in Access?

substitute YourTable with any table you'd like:

select "a" from YourTable
union
select "b" from YourTable

I would expect this to work with the result:

a
b

Now, try the same thing by adding another union to the query

select "a" from YourTable
union
select "b" from YourTable
union
select "c" from YourTable

This simple little example illustrates my problem.   My query will fail whether it's this small query or if it's a large, complex query.

The error I get is:
"ODBC--call failed
[MySQL][ODBC 3.51 Driver][mysqld-5.0.26]You have an error in my SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 'b' FROM `YourTable`)) UNION (SELECT 'c' FROM `YourTable`)' at line 1(#1064)

Now, I can paste my above queries directly in the MySQL query browser and it works.  Somehow this is related to Access.  Can anyone reproduce this?

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
It doesn't fail within an Access MDB ... so I would say the issue is related to Access interfacing with MySQL.

mx
0
 
mbizupConnect With a Mentor Commented:
See the following thread:
http://bugs.mysql.com/bug.php?id=7684

It seems to be a bug, but the workaround I've seen in a few places is to use pass-through queries.

From Access's Query designer's toolbar (SQL View):
Query -> SQL Specific -> select "Pass Through"
 
(Disclaimer: I don't use MySQL, so I do not have personal experience with this.)
0
 
mbizupCommented:
Also, based on the above info - using VBA to create your query (embedded SQL) may also work.
One final note, Access queries are bound to these limitations:
http://www.databasedev.co.uk/access_specifications.html#content3
UNION statements fall under the "nested queries" category.
If you have more than 50 UNION statements, the query will fail with a "Query is too complex" error.
0
 
ComputerZorgCommented:
Same problem here, resolved by first making a union query with two tables, after that make a new union between the created unionquery and a new table. Works somehow, although the underlying sql is essentially the same. Good luck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.