Using The New BSP Data

+1 vote
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
asked May 31, 2017 in Smartform by mickweb Novice (250 points)

1 Answer

+1 vote

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;

answered Jun 2, 2017 by mickweb Novice (250 points)
...