SQL Server – Join a Default Value When No Matching Record Exists

sql-server-2005

I need to be able to join to a 'default' row in a table (righttable) only when there is no matching record for both fields in lefttable. Below is the raw contents of the tables 

#lefttable
--------------------
value  program  race
100    gold     1
100    gold     4
100    gold     5


#righttable
-------------------------
commission  program  race
14          GOLD     1
23          GOLD     NULL

In this example, where the race is 1 I need to pick the first row (commission = 14) but if the race is anything other than 1 I need to pick the NULL row that matches the other field (commission = 23)

The problem I'm having is that my query picks the null record and the matching record when joining, resulting in duplication…

SELECT #lefttable.race,
       totalvalue = SUM(value),
       commission = SUM(value * (commission * 0.01))
  FROM #lefttable
  LEFT JOIN #righttable  ON #lefttable.program = #righttable.program
                        AND #lefttable.race = ISNULL(#righttable.race, #lefttable.race)
 GROUP BY #lefttable.race;

This results in the below

--------------------
race  totalvalue  commission
1     200         37.00
3     100         23.00
4     100         23.00

The totalvalue is double what it should be (and commission incorrect) because the join has created two rows for race 1 (one for each row in righttable)

Ideally I want to be able to solve this problem entirely within the join clause rather than having to add messy cludges to the select clause, or additional joins, or views, or modify righttable to create a row for each race regardless.

I have tried different ways of constructing the join clause but I can't quite wrap my head around how to say "Give me the row with the null race only if there is no row that matches the race in lefttable" using a sql join.

Is this possible?

Best Answer

You should be able to get it by using an OUTER APPLY join plus TOP 1

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

returns:

commission | program | race
---------: | :------ | ---:
        14 | gold    |    1
        23 | gold    | null

but you just need the first row, that you can get by using a TOP 1 in this way:

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

db<>fiddle here

Related Question