clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335821 fiddles created (27453 in the last week).

create table lefttable (value int, program varchar(10), race int); create table righttable (commission int, program varchar(10), race int); insert into lefttable values (100, 'gold', 1), (100, 'gold', 4), (100, 'gold', 5); insert into righttable values (14, 'gold', 1), (23, 'gold', null);
5 rows affected
 hidden batch(es)


select * from righttable r where (r.race=1 and r.program = 'gold') or r.race is null order by case when r.race is null then 1 else 0 end
commission program race
14 gold 1
23 gold
 hidden batch(es)


select l.race, totalvalue = sum(value), commission = SUM(value * (commission * 0.01)) from lefttable l outer apply (select top 1 commission from righttable r where (r.race=l.race and r.program = l.program) or r.race is null order by case when r.race is null then 1 else 0 end) r group by l.race
race totalvalue commission
1 100 14.00
4 100 23.00
5 100 23.00
 hidden batch(es)