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

value  program  race
100    gold     1
100    gold     4
100    gold     5

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?

  • 1
    I'm giving you a +1 for an interesting question (it had me stumped - but I like McNets' solution), but I'm also giving out to you! You have > 500 points here (and more points on StackExchange, Super User and Server Fault so you should know that images are frowned upon here for reasons outlined in this link. In future, for your database questions, please provide DDL and DML as formatted text, or better still in a fiddle!
    – Vérace
    Jan 7 '20 at 15:35
  • Thanks. Sorry, I was in a hurry to get the question posted and using images seemed like the quickest way. I will read that link when I get a chance, and figure out how to do it properly (perhaps by editing my question)
    – MrVimes
    Jan 7 '20 at 17:20
  • To be fair it's images of code that's frowned upon. I posted images of the data, not the code, because I didn't know how to format it in a table when pasting in. I will figure it out.
    – MrVimes
    Jan 8 '20 at 9:26
  • And if one wishes to create a fiddle with that data? You have to go back and forth between your image and the fiddle itself. With the data as text, the person trying to help can copy and paste it at the bottom of a text field and then do CREATE TABLE foo (... and then just look down or Del the fields into position - less likelihood of errors creeping in. Or to copy and paste it into MySQL Workbench for example? Indeed, the first point in the accepted answer (556 upvotes) says Code or sample data in images can't be copied and pasted into an editor - so it's both code and data! :-)
    – Vérace
    Jan 8 '20 at 16:49
  • @Vérace, ok I understand. Have replaced my data images with tables.
    – MrVimes
    Jan 8 '20 at 22:53

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

    righttable r 
    (r.race=1 and r.program = 'gold')
    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

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

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

db<>fiddle here

  • Thankyou! That worked in my test data, and I can see how to make it work in my real-world situation.
    – MrVimes
    Jan 7 '20 at 15:06
  • 1
    I'm glad to help.
    – McNets
    Jan 7 '20 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.