Let's say my table and data as following,declare @tH table (idx int, collector varchar(10), receiptNo varchar(16), sysNo varchar(16), payerID int);insert into @tH values(1, '1925', '018567', '00001', 10);insert into @tH values(2, '1925', '018568', '00002', 9);insert into @tH values(3, '1925', '018569', '00003', 13);/*idx is a primary keyreceiptNo is a unique keysysNo is a unique keyrelationship @tH to @tD is 1 to many*/declare @tD table (tH_idx int, paymentType varchar(10), amount decimal(10,2));insert into @tD values(1, '80090', 320.10);insert into @tD values(1, '80091', 20.50);insert into @tD values(1, '80097', 400.10);insert into @tD values(2, '80090', 400);insert into @tD values(2, '80091', 95.60);insert into @tD values(3, '80092', 70.40);insert into @tD values(3, '80094', 300.00);insert into @tD values(3, '80099', 400.10);/*@tD(tH_idx) is a foreign key to @tH(idx)*/
my expected result as following,idx | collector | receiptNo | sysNo | payerID | totalAmount-----------------------------------------------------------------------------1 1925 018567 00001 10 740.702 1925 018568 00002 9 495.603 1925 018569 00003 13 770.50
need help to built SQL statement