Using The New BSP Data

Question

Firstly a big thanks to Colin for the new betfair BSP data.
That will certainly greatly assist in the researching of
past profitable exchange backing and laying strategies.

But being a newish sql student to sql I am struggling
to retrieve BSP data in my queries.

Let's assume an imaginary sample problem that requires
data from three different tables.

#1 - historic_races
#2 - historic_runners
#3 - historic_betfair_win_prices

For the sake of argument say I want a simple list
of all Michael Soute ( trainer_id =152) runners at Newmarket

For each runner I want a row containing

A #1 - meeting_date
B #1 - sheduled_time
C #2 - name (horse name)
D #2 - finish_position
E #2 - starting_price_decimal
F #3 - bsp

It is probably the need for more complicated
joins on three tables that is throwing me off.

Can someone please advise of the correct sql to use?

Thanks in advance

Answer 1

Prompted by an email from Colin I have now noted the new historic_betfair_win_prices pdf which contains an example of pulling BSP data.

I used that as a base ( the key new bit to me was the format of the join command )

So to answer my own original question above in case it helps others in future here is my sql to achieve an answer to   the example / demo question above.

select meeting_date,scheduled_time,name,finish_position,starting_price_decimal,bsp
from historic_races
join historic_runners using (race_id) join
historic_betfair_win_prices ON race_id=sf_race_id and
runner_id = sf_runner_id 
where course='newmarket' and trainer_id=152
order by scheduled_time asc;