Join two tables

[sqlnew]

Hi,

Please let me know if the below case is possible:

I have table t1 and t2 with the following structure.

T1
EmpID Bonus Amount

1 10 100
1 20 100
2 5 30
2 5 70

T2 (Bonus in T2 is the Total bonus for each employee)

EmpID Bonus C1 C2

1 30 2 3
2 10 4 5

I have to use these two tables to get the following result set:

EmpID Bonus Amount C1

1 30 200(100+100) 2
2 10 100(30+70) 4

I don’t know why the table structure is like this but is it possible to join these two tables based on EmdpID?

 

[gmmastros]

There are several ways to do this. I would suggest a derived table approach.

From T1, you want to sum the Bonus and the Amount, so....

[tt]
Select EmpId,
Sum(Bonus) As Bonus,
Sum(Amount) As Amount
From T1
Group By EmpId
[/tt]

Now, to add the C1 value from T2...

Select T1Total.EmpId,
       T1Total.Bonus,
       T1Total.Amount,
       T2.C1
From   T2
       Inner Join (
          [green]Select EmpId, 
                 Sum(Bonus) As Bonus,
                 Sum(Amount) As Amount
          From   T1
          Group By EmpId[/green]
          ) As T1Total
          On T1Total.EmpId = T2.EmpId

Notice how the first query is embedded in the 2nd query. This is considered a derived table.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom