Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join two tables

Status
Not open for further replies.

sqlnew

Programmer
Jul 22, 2010
6
US
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?
 
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...

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top