select from multiple tables in db from a newbie

select from multiple tables in db

[ski442]

Hi Guys.

I have a db with 34 tables with all the same feilds, and would like to select 1 columb from all these tables. Is this possible from a select query, if so a example would be great.

You may well ask why have i 34 tables all the same, but i thought is was a good idea to set out the tables as per my catagories when i was constructing them. But now i know i can do them all from 1 table with multiple fields. Thats what I get for not doing my resaerch. "Ha Ha i hear you say"

So some much needed help please guys.

[WesleyC]

Try...

SELECT table1.my_column, table2.my_column FROM table1, table2

[Habtom]

But now i know i can do them all from 1 table with multiple fields.

Well, the time you spend merging them now could as well save you your future time of maintaining all those tables. How would you handle it when a field is to be added, or field type is to be modified. :)

Adding up to the above solution

SELECT table1.my_column, table2.my_column FROM table1, table2

You can put it in the following way:
SELECT T1.my_column, T2.my_column FROM table1 T1, table2 T2

Also you can reassign the columns a different name of your choice as it can help you use them easily.
SELECT T1.my_column as T1_column, T2.my_column as T2_column FROM table1 T1, table2 T2

Habtom

[SteveLetwin]

Do you want the result to be rows that are 34 columns wide? Or do you want the result to be 1 column wide with however many rows there are in the 34 databases?

If it's the former go with the previous suggestions. If it's the latter then you can use:

select col_a from table1
union
select col_b from table2
union 
select col_c from table3
...