Dedub query

I am trying to join two table using a primary key, my problem is that one table has multiple listing of that primary key, I only want to join to the primary key once. Can anyone show me how this can be done?

Table1

acct_no sale_am tran_cd

123 50 2

123 54 1

113 20 9

124 30 7

Table2

acct_no exp_am res_am

123 50 20

113 24 30

124 60 10

What I need:

acct_no sum(sale_am) sum(exp_am) sum(res_am)

123 104 50 20

113 20 24 30

124 30 60 10

Thanks

There are several possibilities. And each one could provide different resultsets.

Please let us know what is your expected output and we can better assist you.

|||

Assuming you want the latest tran_cd value, you can just use a derived table and the ROW_NUMBER() windowed function:

select *

from (select acct_no, sale_am, row_number() over (partition by acct_no order by tran_cd desc) as rowNbr

from table1) as table1
join table2
on table1.acct_no = table2.acct_no

and table1.rowNbr = 1

If this is something that you do often, especially something that needs a lot of performance, I might consider implementing a current_row_flag in your table to denote the row you want to usually use (especially if those rows don't change much)

|||

Code Snippet

createtable #Table1( acct_no int, sale_am money, tran_cd int)

insertinto #Table1

select 123, 50, 2

union allselect 123, 54, 1

union allselect 113, 20, 9

union allselect 124, 30, 7

createtable #Table2( acct_no int, exp_am money, res_am money)

insertinto #Table2

select 123, 50, 20

union allselect 113, 24, 30

union allselect 124, 60, 10

select t1.acct_no,sum(t1.sale_am)as sale_am,

sum(t2.exp_am)as exp_am,sum(t2.res_am)as res_am

from #Table1 t1

innerjoin #Table2 t2

on t1.acct_no = t2.acct_no

groupby t1.acct_no

sql