Range comparison between tables

Question

I have two tables like this:

Runners:  
Name|Date    |FromDist|ToDist|RaceN  
Al  |1/1/2017|1       |9     |axe  
CC  |1/1/2017|11      |12    |axe   
Bm  |2/1/2017|6       |2     |Charity  



RaceStandards:  
RaceN  |Date    |FromDist|ToDist|Direction|Rank  
axe    |1/1/2017|1       |10    |+        |1
Charity|2/1/2017|10      |4     |-        |2

The race standards table gives the direction and distance ranges for each race. The runners table gives the distances ran by each runner.
Question/Issue/What I want to do:
1. Get the direction
2. If its +, range should be >=FromDist and <=ToDist; If its -, range should be <=FromDist and >=ToDist
3. Compare the distance range of Runners FromDist and ToDist based on steps 1 and 2. Assign the Rank number of that race if they fall in range, otherwise assign 0
4. If Runner is out of Range the value can be zero

So the end result would be something like this:

Runners:  
    Name|Date    |FromDist|ToDist|RaceN   |Rank  
    Al  |1/1/2017|1       |9     |axe     |1
    CC  |1/1/2017|11      |12    |axe     |0
    Bm  |2/1/2017|6       |2     |Charity |2  

There would be hundreds of rows in each table so I would want it dynamic. There could be overlaps and stuff but I just need a headstart for now, once I get a base code I can try to expand from there. I would appreciate any help. I tried to give as much detail, if more is required please ask.

Answer 1

If I understand correctly, this should give you what you're looking for.

SELECT R.*, COALESCE(RS.Rank, 0) AS Rank
FROM Runners AS R
  LEFT OUTER JOIN RaceStandards AS RS
    ON R.RaceN = RS.RaceN
    AND (
      (RS.Direction = '+' AND R.FromDist >= RS.FromDist AND R.ToDist <= RS.ToDist)
      OR (RS.Direction = '-' AND R.FromDist <= RS.FromDist AND R.ToDist >= RS.ToDist)
    )