By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
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 | null |
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 |