# Range comparison between tables

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.

``````SELECT R.*, COALESCE(RS.Rank, 0) AS Rank