30 tables I need the Same 5 fields from wach of them!!!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 30 different tables and i need to pull the date, time,ID, plus a
couple other columns from each.
Can i do it in one step??
So i want to tell the query to go pick these 5 columns from all the
different tables.
Thanks Matt
 
You need a union query ...

SELECT Field1, Field2 FROM FirstTable
UNION SELECT Field1, Field2 FROM SecondTable
etc., etc., etc.

If the table names are numbered, something like Table1, Table2, Table3 etc.,
it might be possible to write some code to build the SQL statement, and it
might be a little faster, or at least a little less tedious, than typing 30
SELECT statements.

Do you need me to tell you that this is not a good database design?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
hey Brendan
Thanks for your help i will give it a try!

Nope you dont have to tell me its bad design i said the same thing but you
have to work with what was developed!!!!
I'll let ya know
 
Thats works well
But is there a way i will be able to select some coloums that will be unique
to there own table?
thanks
 
Well, each select clause must have the same number of columns. The columns
from each table don't have to have the same names, but they must have
compatible data types. So for example if the second table contains a numeric
field that the first table doesn't, and the third table contains a text
field that the first and second tables don't, the UNION query might look
something like ...

SELECT FirstField, 0 AS SecondField, "" AS ThirdField FROM FirstTable
UNION SELECT FirstField, SecondField, "" AS ThirdField FROM SecondTable
UNION SELECT FirstField, SecondField, ThirdField FROM ThirdTable

BTW: The default behaviour of a UNION query is to include only unique
records - if a row from the second table exactly matches a row from the
first table, it will not be included. If you want to include duplicates, you
need to include the ALL predicate ...

SELECT etc FROM FirstTable
UNION ALL SELECT etc FROM SecondTable

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thansk for your help!!!

Brendan Reynolds said:
Well, each select clause must have the same number of columns. The columns
from each table don't have to have the same names, but they must have
compatible data types. So for example if the second table contains a numeric
field that the first table doesn't, and the third table contains a text
field that the first and second tables don't, the UNION query might look
something like ...

SELECT FirstField, 0 AS SecondField, "" AS ThirdField FROM FirstTable
UNION SELECT FirstField, SecondField, "" AS ThirdField FROM SecondTable
UNION SELECT FirstField, SecondField, ThirdField FROM ThirdTable

BTW: The default behaviour of a UNION query is to include only unique
records - if a row from the second table exactly matches a row from the
first table, it will not be included. If you want to include duplicates, you
need to include the ALL predicate ...

SELECT etc FROM FirstTable
UNION ALL SELECT etc FROM SecondTable

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Back
Top