title: Join Clause With a XOR Statement
body: <p>I am doing a join and I can't seem to make this XOR to properly work.</p>
  
  <pre><code>SELECT t1.COMPANY, t1.MILES,
     CASE WHEN t2.MILES IS NULL THEN t3.MILES
          ELSE t2.MILES
     END AS MILES2,
     CASE WHEN t2.MILES = t1.MILES AND t2.MILES != 9999 THEN t2.FLATRATE
          ELSE t3.RATEBASIS
     END AS RATE           
  FROM TABLE1 AS t1
      LEFT JOIN TABLE2 AS t2 
          ON t1.[COMPANY] = t2.[COMPANYCODE] AND (t1.[MILES] = t2.[MILES])
      INNER JOIN (
           SELECT TOP 1 TRUCKERCODE, MILES, RATEBASIS, FLATRATE FROM TABLE2 WHERE MILES = 9999
                 ) AS t3 
     ON t1.[COMPANY] = t3.[COMPANYCODE]
  </code></pre>
  
  <p>I need the ON clause to join if the miles are the same then get the given fields that match otherwise the default data I need to get out of the second table is where the miles is equal to 9999. Right now with that ON clause I get many extra rows where the MILES equals lets say 45, it gets the data from TABLE2 where miles equals 45 and all the data where miles equals 9999. I need it to do one or the other but not both. This is what my tables would look like</p>
  
  <pre><code>TABLE1                          TABLE2
  ID COMPANY MILES ETC            ID COMPANYCODE MILES RATE
  1  ILLINI  50                   1  ILLINI      50    3.2
  2  ILLINI  110                  2  ILLINI      110   5.2
  3  ILLINI  150                  3  ILLINI      150   2.4
  4  ILLINI  200                  4  ILLINI      200   1.9
  5  ILLINI  250                  5  ILLINI      9999  1.5
  6  ILLINI  300                       
  7  ILLINI  350
  8  ILLINI  400
  9  ILLINI  450
  10 ILLINI  500
  
  Desired Output
  COMPANY MILES MILES2 RATE
  ILLINI  50    50    3.2
  ILLINI  110   110   5.2
  ILLINI  150   150   2.4
  ILLINI  200   200   1.9
  ILLINI  250   9999  1.5
  ILLINI  300   9999  1.5                    
  ILLINI  350   9999  1.5
  ILLINI  400   9999  1.5
  ILLINI  450   9999  1.5
  ILLINI  500   9999  1.5
  </code></pre>