Sql join with three tables and multiple rows

Question

I have three tables like this:

Table Runners:

This table saves the runner info and the length of miles

ID|Name|BMP  |EMP  |Vendor  
1 |Alex|12.25|22.25|Heinz  
2 |Abby|0.00 |3.55 |Uni  
3 |Marx|5.55 |10.55|Jam 

Table RaceDetails: This table saves the direction(most races have up and down in which case the range switches)

ID|Dir|BMP  |EMP  
1 |U  |12.26|12.33  
1 |U  |12.33|12.47  
1 |U  |21.12|21.99  
1 |U  |22.00|22.44  
1 |U  |22.15|22.36  
2 |U  |0.00 |2.55  
2 |U  |2.55 |4.56  
2 |D  |4.56 |2.55  
2 |D  |2.55 |0.00   
3 |U  |5.55 |9.55  
3 |U  |9.55 |11.69  
3 |D  |11.69|9.55  
3 |D  |9.55 |-0.12  

Table RaceSense:

ID|BMP  |EMP  |SENSOR  
1 |12.25|20.33|1  
1 |20.33|22.25|3  
2 |0.00 |3.55 |2  
3 |5.55 |9.55 |2  
3 |9.55 |10.55|5

I am doing a join between them like this:

Select    distinct * 
from      Runners a 
left join RaceDetails b 
on        a.ID=b.ID 
left join RaceSense c 
on        a.ID=c.ID  

This is giving me 22 rows because it duplicates data from Racedetails because of multiple rows for same ID in RaceSense table

I also tried this:

Select    distinct * 
from      Runners a 
left join RaceDetails b 
on        a.ID=b.ID 
left join RaceSense c 
on        a.ID=c.ID 
and      (b.BMP+b.EMP)/2>=c.BMP 
and      (b.BMP+b.EMP)/2<=c.EMP  

This gives me 8 rows not 13 because some of the average fall outside the actual range from the RaceSense Table

I want 13 rows with no duplication, Like this:

ID|Name  |BMP  |EMP  |VENDOR|SENSOR  
1 |Alex  |12.26|12.33|Heinz |1  
1 |Alex  |12.33|12.47|Heinz |1  
1 |Alex  |21.12|21.99|Heinz |3  
1 |Alex  |22.00|22.44|Heinz |3  
1 |Alex  |22.15|22.36|Heinz |3  
2 |Abby  |0.00 |2.55 |Uni   |2 
2 |Abby  |2.55 |4.56 |Uni   |2 
2 |Abby  |4.56 |2.55 |Uni   |2
2 |Abby  |2.55 |0.00 |Uni   |2
3 |Marx  |5.55 |9.55 |Jam   |2 
3 |Marx  |9.55 |11.69|Jam   |5  
3 |Marx  |11.69|9.55 |Jam   |5  
3 |Marx  |9.55 |-0.12|Jam   |2   

The way I want to do it is:

1.if the BMP and EMP in Details table are within The Sense table BMP and EMP then that is good.
2.If either BMP or EMP in Details is outside either the BMP or EMP in Sense table then take the average of BMP and EMP in Details and ensure it is within the range of Sense table.
3.If the average value is also out of range then I want to expand the Sense table BMP and EMP by 0.1
4.Otherwise give me a null

Any ideas?

Answer 1

The data upload process should assign the accurate min and max values to BMP and EMP in the Sense table(they remain approximations). I would be working on the upload process to make it more accurate. However, to put this question to rest:

What I did to fix this was to find the min and max from the Details table and update the Sense table to match the actual range(how it should have been in the first place). However, the problem I had was that when I use this:

(b.BMP+b.EMP)/2>=c.BMP and (b.BMP+b.EMP)/2<=c.EMP

If the average is say 2.45 and the Sense table shows two ranges like this: 0 to 2.45 and 2.45 to 5 for the same ID then it would give me a duplicate. So for now I changed the query to this and it works:

Select distinct *
from Runners a
left join RaceDetails b
on a.ID=b.ID
left join RaceSense c
on a.ID=c.ID
and (b.BMP+b.EMP)/2>=c.BMP
and (b.BMP+b.EMP)/2<c.EMP --removed = sign 

If somebody comes up with a query that would work without updating the table in the long run. I would select that answer.