SELECT FROM two tables - SQL Server Forums

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 FROM two tables

Author

Topic

CtrlAltPhreak
Starting Member

3 Posts

Posted - 2009-04-03 : 04:50:13

I am creating a .net app which is in essence a database front end.

One of the options in the app is "View All", this does a select statement on a main table joined into some other tables with no WHERE clause on it.

Over time this operation is obviously going to return more and more values and slow the app down. Due to this I've decided to implement an archiving strategy where by I have a second table identical to the first in structure call Archive. Every month or so I will be inserting old rows into the archive table and deleting them from the main table.

My problem has arisen due to the fact that, under certain conditions (historical reporting etc...) I will want to return values from both tables.

Is this possible with a single SELECT query or will I have to perform two queries (one on the main table and one on the archive)?

Both tables have an identical structure and there will be no duplicate entries between the tables.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 05:14:15

select columns from main_table
union all
select columns from archive_table

Madhivanan

Failing to plan is Planning to fail

CtrlAltPhreak
Starting Member

3 Posts

Posted - 2009-04-03 : 06:17:15

I assume I can put joins in both the first and second select part?

e.g. SELECT main_table.EmployeeID, main_table.DepartmentID, employee_table.Name, department_table.Name FROM main_table INNER JOIN employee_table ON main_table.EmployeeID=employee_table.ID INNER JOIN department_table ON main_table.DepartmentID=department_table.ID
UNION ALL
SELECT archive_table.EmployeeID, archive_table.DepartmentID, employee_table.Name, department_table.Name FROM archive_table INNER JOIN employee_table ON archive_table.EmployeeID=employee_table.ID INNER JOIN department_table ON archive_table.DepartmentID=department_table.ID

The archive table will only archive records from the main table. (e.g. employee_table and department_table will remain the same)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources