select query from multiple 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 2005 Forums

Transact-SQL (2005)

select query from multiple tables

Author

Topic

mcbulan
Starting Member

7 Posts

Posted - 2010-09-01 : 09:34:37

Hello all
I have 5 tables which has same colunms and column names. i would like to select from these tables in a same select query.
Please look at the tables :

dep_chemistry : kid, name,surname,email,active
dep_civileng : kid, name,surname,email,active
dep_electronics : kid, name,surname,email,active
dep_medical : kid, name,surname,email,active
dep_mechanical : kid, name,surname,email,active

i would like to create a select command that select from all these tables in one query.

just like :
"select kid,name from dep_chemistry,dep_civileng,dep_electronics,dep_medical,dep_mechanical where active = 1"

mcb

CSears
Starting Member

39 Posts

Posted - 2010-09-01 : 09:41:17

If the tables are Identical you can use the UNION or UNION ALL statement.

select kid,name from dep_chemistry where active = 1
UNION
select kid,name from dep_civileng where active = 1
UNION
select kid,name from dep_electronics where active = 1
UNION
select kid,name from dep_medical where active = 1
UNION
select kid,name from dep_mechanical where active = 1

mcbulan
Starting Member

7 Posts

Posted - 2010-09-01 : 10:26:00

thank you very much for help CSears.

mcb

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-01 : 10:40:57

Probably a little advanced but you could make a view over the tables. Looks like it's already set up for partitioning.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources