T-SQL querying multiple tables based on certain conditions

[lbala]

Hi,

I have a situation where i need to query multiple tables (12 tables) within a database for records containing values that are listed in another table and also based on a certain conidition.

Specifically, i have a EMP table with a list of employee names, date of joining, office address etc. I have 12 other tables that contain detailed information for all employees split into 12 monthly tables, based on date of joining.

I need a query to list all information in these 12 tables for all employees that are listed in table EMP for employees that joined before certain date (the date field and EMP number is common to the EMP field and all the 12 tables).

Can you help me?
thnak you in advance!

 

[r937]

use twelve LEFT OUTER JOINs

r937.com | rudy.ca

 

[gmmastros]

Do I understand you correctly.

You have 1 EMP table and 12 other monthly tables. Each employee would only have 1 record in the other 12 tables?

If this is true, you could left join the tables and use the coalesce function to return the data. Something like this...

[tt][blue]
Select EMP.column1,
EMP.Column2,
Coalesce(emp_Jan.SomeColumn, emp_Feb.SomeColumn, emp_Mar.SomeColumn)
From emp
Left Join emp_Jan
On emp.EmpNumber = Emp_Jan.EmpNumber
Left Join Emp_Feb
On Emp.EmpNumber = Emp_Feb.EmpNumber
Left Join Emp_Feb
On Emp.EmpNumber = Emp_Mar.EmpNumber
Where Emp.Join_Date < '20070101'
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom