SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SELECT FROM two tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CtrlAltPhreak
Starting Member

3 Posts

Posted - 04/03/2009 :  04:50:13  Show Profile  Reply with Quote
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

India
22713 Posts

Posted - 04/03/2009 :  05:14:15  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
select columns from main_table
union all
select columns from archive_table


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

CtrlAltPhreak
Starting Member

3 Posts

Posted - 04/03/2009 :  06:17:15  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000