sql - How to Correctly Sum Totals from a Table That Must be Joined to Another Table that Causes Duplicates -

i have 2 tables following:

pay_table

emplid  pay 123     100 123     150 123     150 

deduction_table

emplid  deduction 123     15 123     30 

and want result following:

total_pay 400 

i result simple query , feel i'm missing obvious way it, can't seem figure out is.

for instance, query returns 800 because every row in pay_table being duplicated when joined deduction_table:

select sum(pay) total_pay  pay_table join deduction_table using(emplid); 

and query returns 250 because distinct keyword causes second 150 value in pay_table ignored:

select sum(distinct pay) total_pay  pay_table join deduction_table using(emplid); 

there several ways this, looking simplest way return result of 400.

here code create example tables make easier:

with  pay_table (   select 123 emplid, 100 pay dual   union    select 123, 150 dual   union    select 123, 150 dual ), deduction_table (   select 123 emplid, 15 deduction dual    union   select 123, 30  dual ) 

it's unclear need, since example doesn't make use of deduction_table table, believe you'll want aggregate before join:

;with pay (select emplid,sum(pay) pay               pay_table               group emplid              )      ,ded (select emplid,sum(deduction) ded               deduction_table               group emplid               ) select * pay left join ded   on pay.emplid = ded.emplid