Union query

[Paradigm]

I am using Access 2K as a front end to a MYSQL database.

I am trying to run a Union query on the MYSQL database. The query is (much
simplified)
SELECT [faxid] as ID from faxdata UNION
SELECT [letid] as ID from letdata UNION
SELECT [memoid] as ID FROM MEMODATA;

I get an ODBC error. The same query runs when the backend files are MDB
files and it runs with MYSQL if I only combine 2 tables.
Is there some limit with MYSQL on being only able to use a UNION on 2
tables.
Alex

[Jeff North]

On Fri, 28 Jan 2005 14:16:56 GMT, in mailing.database.mysql "Paradigm"
<al********@hotmail.com> wrote:

| I am using Access 2K as a front end to a MYSQL database.
|
| I am trying to run a Union query on the MYSQL database. The query is (much
| simplified)
| SELECT [faxid] as ID from faxdata UNION
| SELECT [letid] as ID from letdata UNION
| SELECT [memoid] as ID FROM MEMODATA;
|
| I get an ODBC error. The same query runs when the backend files are MDB
| files and it runs with MYSQL if I only combine 2 tables.
| Is there some limit with MYSQL on being only able to use a UNION on 2
| tables.

Each select statement in a union query must return the same number of
columns and the same data type for each column. If your first select
statement returns 3 fields (int, char, blob) then so must the other
select statements.

If any table is returning more columns than the first select statement
then you'll need to add (as I call them) virtual fields (f3 in the
following example)
select f1, f2, 0 as f3 from t1 union
select f1, f2, f3 from t2 union
select f1, f2, 0 as f3 from t3

http://dev.mysql.com/doc/mysql/en/union.html

---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Paradigm]

I have made sure that the column numbers and types are the same. I have 4
tables that I want to UNION into a single query. I simplified this below
into 3 tables and a single field. The example does not work. I can reduce
the example to any 2 of the tables and it then does work. The ID field here
is an int(11) auto_increment type in MYSQL and was an Autonumber field in
Acces.
I am converting an Access application into a MYSQL backend. The same query
works if the tables are MDB tables but not if they are MYSQL tables.
Alex

SELECT [faxid] as ID from faxdata UNION
SELECT [letid] as ID from letdata UNION
SELECT [memoid] as ID FROM MEMODATA;

"Jeff North" <jn****@bigpond.net.au> wrote in message
news:v7********************************@4ax.com...

On Fri, 28 Jan 2005 14:16:56 GMT, in mailing.database.mysql "Paradigm"
<al********@hotmail.com> wrote:

| I am using Access 2K as a front end to a MYSQL database.
|
| I am trying to run a Union query on the MYSQL database. The query is (much| simplified)
| SELECT [faxid] as ID from faxdata UNION
| SELECT [letid] as ID from letdata UNION
| SELECT [memoid] as ID FROM MEMODATA;
|
| I get an ODBC error. The same query runs when the backend files are MDB
| files and it runs with MYSQL if I only combine 2 tables.
| Is there some limit with MYSQL on being only able to use a UNION on 2
| tables.

Each select statement in a union query must return the same number of
columns and the same data type for each column. If your first select
statement returns 3 fields (int, char, blob) then so must the other
select statements.

If any table is returning more columns than the first select statement
then you'll need to add (as I call them) virtual fields (f3 in the
following example)
select f1, f2, 0 as f3 from t1 union
select f1, f2, f3 from t2 union
select f1, f2, 0 as f3 from t3

http://dev.mysql.com/doc/mysql/en/union.html

---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Jeff North]

On Sat, 29 Jan 2005 10:05:28 GMT, in mailing.database.mysql "Paradigm"
<al********@hotmail.com> wrote:

| I have made sure that the column numbers and types are the same. I have 4
| tables that I want to UNION into a single query. I simplified this below
| into 3 tables and a single field. The example does not work. I can reduce
| the example to any 2 of the tables and it then does work. The ID field here
| is an int(11) auto_increment type in MYSQL and was an Autonumber field in
| Acces.
| I am converting an Access application into a MYSQL backend. The same query
| works if the tables are MDB tables but not if they are MYSQL tables.
| Alex
|
| SELECT [faxid] as ID from faxdata UNION
| SELECT [letid] as ID from letdata UNION
| SELECT [memoid] as ID FROM MEMODATA;
[Kicking myself for missing the obvious]
Remove the square brackets from the field names, they are an Access
thing.
| "Jeff North" <jn****@bigpond.net.au> wrote in message
| news:v7********************************@4ax.com...
| > On Fri, 28 Jan 2005 14:16:56 GMT, in mailing.database.mysql "Paradigm"
| > <al********@hotmail.com> wrote:
| >
| > >| I am using Access 2K as a front end to a MYSQL database.
| > >|
| > >| I am trying to run a Union query on the MYSQL database. The query is
| (much
| > >| simplified)
| > >| SELECT [faxid] as ID from faxdata UNION
| > >| SELECT [letid] as ID from letdata UNION
| > >| SELECT [memoid] as ID FROM MEMODATA;
| > >|
| > >| I get an ODBC error. The same query runs when the backend files are MDB
| > >| files and it runs with MYSQL if I only combine 2 tables.
| > >| Is there some limit with MYSQL on being only able to use a UNION on 2
| > >| tables.
| >
| > Each select statement in a union query must return the same number of
| > columns and the same data type for each column. If your first select
| > statement returns 3 fields (int, char, blob) then so must the other
| > select statements.
| >
| > If any table is returning more columns than the first select statement
| > then you'll need to add (as I call them) virtual fields (f3 in the
| > following example)
| > select f1, f2, 0 as f3 from t1 union
| > select f1, f2, f3 from t2 union
| > select f1, f2, 0 as f3 from t3
| >
| > http://dev.mysql.com/doc/mysql/en/union.html
| >
| > ---------------------------------------------------------------
| > jn******@yourpantsyahoo.com.au : Remove your pants to reply
| > ---------------------------------------------------------------
|

---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------