|
CtrlAltPhreak
Starting Member
3 Posts |
 Posted - 04/03/2009 : 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.
|
|
|
CtrlAltPhreak
Starting Member
3 Posts |
Posted - 04/03/2009 : 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) |
 |
|
SELECT FROM two tables
