jc524400


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




Re: Dedub query

Arnie Rowland


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.







Re: Dedub query

Louis Davidson

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)







Re: Dedub query

DaleJ

Code Snippet

create table #Table1( acct_no int, sale_am money, tran_cd int)

insert into #Table1

select 123, 50, 2

union all select 123, 54, 1

union all select 113, 20, 9

union all select 124, 30, 7

create table #Table2( acct_no int, exp_am money, res_am money)

insert into #Table2

select 123, 50, 20

union all select 113, 24, 30

union all select 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

inner join #Table2 t2

on t1.acct_no = t2.acct_no

group by t1.acct_no