select same field from many tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

select same field from many tables

Author

Topic

lancepr
Starting Member

17 Posts

Posted - 2002-11-12 : 09:58:52

I have many tables
a_visitors
b_visitors
....
z_visitors

each table will have a field named f_status

How can I write a statement to select this feild from all the tables?

Thanks,
Lance

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-12 : 10:04:12

quote:

I have many tables
a_visitors
b_visitors
....
z_visitors

each table will have a field named f_status

How can I write a statement to select this feild from all the tables?

Thanks,
Lance

Depends how you want the output.

SELECT f_status
from a_visitors

UNION ALL

SELECT f_status
from b_visitors

...

Is one way. I assume that you want the f_status for a certain visitor though across each of the tables, in which case you would have to join them together on some kind of visitor id.

PS
Have you considered designing your database to have just one table, with a field in it that determines the type of visitor?

Edited by - mr_mist on 11/12/2002 10:05:23

lancepr
Starting Member

17 Posts

Posted - 2002-11-12 : 10:07:43

Actually I just need all of tis data at once, would I have to type each table into the statement.
I guess what I would really like to do is select that field from all tables ending in _visitors, is that possible?

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-12 : 10:19:04

quote:

Actually I just need all of tis data at once, would I have to type each table into the statement.
I guess what I would really like to do is select that field from all tables ending in _visitors, is that possible?

I daresay you could do it with some kind of loop and a bit of dynamic SQL. However, I really think that in the long run you would be better off not having so many _visitors tables.

(That is, I'm assuming the following..)

You have a number of tables like this

a_visitors
visitor_name
visitor_time
visitor_carcolour
visitor_cat_shape
visitor_status

b_visitors
visitor_name
visitor_time
visitor_carcolour
visitor_dog_shape
visitor_status

... and so on

And you might be better off with

TABLE: visitors
visitor_name
visitor_time
visitor_carcolour
visitor_animal_type
visitor_animal_shape
visitor_status

TABLE :visitor_codes
visitor_name
visitor_carcolour
visitor_code

Where visitor_code is a,b,c,d etc.

Edited by - mr_mist on 11/12/2002 10:24:20

Edited by - mr_mist on 11/12/2002 10:25:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources