Linking Three Tables

Question

If I use the following script I can link everything between the two main tables using the race id

SELECT * FROM historic_races join historic_runners using (race_id)

However I am having trouble adding the historical Betfair SP table to these two so I can link the three tables together although I guess that the equivalent of the race_id in the above two tables would be the column  sf_race_id in the historic_betfair_win_prices table

I have tried all kinds of things and keep failing so any advice would be appreciated

Answer 1

I think you can just use another JOIN. e.g.

SELECT *
FROM smartform.historic_races
        JOIN smartform.historic_runners USING (race_id)
        JOIN historic_betfair_win_prices ON race_id=sf_race_id and runner_id = sf_runner_id

Comments

Many Thanks starkingdom for the suggestion/solution however this has now created another problem for me as the output file is too big to export even as a csv file from Workbench and is well over a million lines meaning things crash after a while.

If I may ask is there any way to break down the data/query output into separate years or more manageable chucks?

Its going to be fun working through all this data because I absolutely love number crunching.

---

Unfortunately I'm not at a computer right now, but have a look at the recent blog post here:

https://blog.betwise.net/2018/04/28/calculating-strike-rate-using-smartform-and-r/

You can ignore the R code if you're using Workbench to export the data. However,  I do provide an SQL query example which is limited by date. In this case,  January 1st, 2009. You could write a query for each year.

Also consider selecting the specific data columns you are interested in, rather than just the star wildcard for everything. This will also assist in limiting the amount of data returned.

---

Thanks for the link...I shall scour previous posts and try and glean  nuggets of wisdom as I progress along the journey learning to construct queries in a meaningful accurate way.