473,847 Members | 2,318 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Union query

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
Jul 23 '05 #1
3 3579
On Fri, 28 Jan 2005 14:16:56 GMT, in mailing.databas e.mysql "Paradigm"
<al********@hot mail.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******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #2
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.c om...
On Fri, 28 Jan 2005 14:16:56 GMT, in mailing.databas e.mysql "Paradigm"
<al********@hot mail.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******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

Jul 23 '05 #3
On Sat, 29 Jan 2005 10:05:28 GMT, in mailing.databas e.mysql "Paradigm"
<al********@hot mail.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.c om...
| > On Fri, 28 Jan 2005 14:16:56 GMT, in mailing.databas e.mysql "Paradigm"
| > <al********@hot mail.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******@yourpa ntsyahoo.com.au : Remove your pants to reply
| > ---------------------------------------------------------------
|


---------------------------------------------------------------
jn******@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
1816
by: Salad | last post by:
A97. Situation: I have 3 tables with a text field in each and a date field in the first 2 tables: Table1 Text1, Date1 Table2 Text2, Date2 Table3 Text3 (no date field) The following makes up a saved query called Query1
5
3301
by: Lyn | last post by:
This one is difficult to explain, so I will cut it down to the basics. I have a major table 'tblA' which has an autonum field 'ID-A' as primary key (of no significance to users). 'tblA' contains many fields including picture and memo fields. The main user-selectable field is 'NameA'. There is also a crossreference table (let's call it 'tblB') which provides a secondary method of accessing records in 'tblA'. The main fields in 'tblB'...
2
4347
by: mattytee123 | last post by:
I have about 20 tables, of which I would like to do a union query and count of how many of each different code there is? The simplified verson of the table is structured like this. Code Count 1234 1 2468 1 1234 1 2468 1
2
9786
by: marco | last post by:
Dear List, as it seems, MS SQL as used in Access does not allow a select INTO within a UNION query. Also, it seems that a UNION query can not be used as a subquery. Maybe my (simplified) problem can avoid these technicalities: the original table has columns A1, A2, B1, B2, C1, C2.
3
2227
by: mikes | last post by:
I have 2 separate queries, which effectively are the same except they draw data from separate tables. Both tables are (design-wise) identical, only the data is different. for each query, there are 2 tables with a standard LEFT JOIN. One field of the query is calculated, looking for a NULL in one table, and then using a field from the second table in that case. One query looks like this: PARAMETERS Text ( 255 ); SELECT...
5
2283
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were noticing some inconsistant data and when I analysed the query produced and opened it from a MS Query it started giving strange results. The first query when run alone returns 22 records, some of which have identical values in all fields. This is 100% correct. The second query returns nothing....
7
3319
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a count for each type so I can group by...etc. The view I have works, but doesn't work when I supplement the query with some functions... they just don't like the UNION. The real problem is I can't change any of the udf's or queries, just the view. The view is inner joined back on to the primary...
5
3852
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data from 2005/01/01 ~ 2007/05/xx in single big table, we try separate this big table into twelve tables and create a view
27
13832
by: MLH | last post by:
How can I turn the following into a make-table query? SELECT & " " & AS Recipient FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID(); UNION SELECT AS Recipient FROM tblVehicleJobs INNER JOIN tblLienHolders ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID WHERE
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10643
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10330
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9477
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7879
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7050
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5716
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.