Matching 'historic_betfair_win_prices' with 'daily_runners.race_id'

Question

I have data on the runners etc. from the 'daily_runners' table using 'race_id'. For example:

Select * from daily_runners

WHERE race_id = '1062243'

This query concerns a race at Wolverhampton on 02-Oct-2021. I have the same for all races on this day. 

I want to match 'bsp' and 'win' with the runners on that day by capturing all data from 'historic_betfair_win_prices' using this query:

SELECT date, sf_race_id, sf_runner_id, sf_name, win, bsp

FROM historic_betfair_win_prices

WHERE date = '2021-10-02'

The latter query, however, does not retrieve data corresponding to the 'race_id' or any data for the other races at Wolverhampton.

How can I retrieve this data?

I appreciate any help you can provide.

Answer 1

I don't think this data is available in historic_betfair_win_prices (but you probably know this already).

The following query gives you the list of runners for that race:

select hu.name
from historic_races hr, historic_runners hu
where hr.race_id = hu.race_id
and  hr.race_id = '1062243'

....while this query returns all rows from historic_betfair_win_prices for that list of runners only:

SELECT * FROM historic_betfair_win_prices
where sf_name in
(select hu.name
from historic_races hr, historic_runners hu
where hr.race_id = hu.race_id
and  hr.race_id = '1062243'
)
order by date

If you look through the data, there's about 9 rows for October 2021, but none for 2nd October 2021 (2021-10-02).