0

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?

  • What do you want your result set to look like? If you're using select distinct * from all tables, you will certainly match 2 ID-1 rows on RaceSense for each ID-1 row found on RaceDetails. However, it sounds like you'd only want 5 total rows for ID-1. What criteria will you use to decide which RaceSense row to return? – Scott Hodgin Nov 2 '17 at 13:46
  • 1
    What's the relationship between RaceDetails and RaceSense? – Scott Hodgin Nov 2 '17 at 14:35
  • Format the results to line up – paparazzo Nov 2 '17 at 14:36
  • @ScottHodgin RaceDetails basically gives you the actual detailed data for every few miles that the sensors are located and as the runner crosses them. The RaceSense table basically tells you an approximate range of values for BMP and EMP where a specific type of sensor is located on a track – SQLserving Nov 2 '17 at 14:42
  • @SQLserving - tell me how you know which RaceSense row to pick for a given RaceDetail row. Take the first row in your result set. How did you know to pick the row with SENSOR 1 instead of the row with SENSOR 3? – Scott Hodgin Nov 2 '17 at 14:47
0

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.

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.