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?