How do i make a master table veiw

Question

Hello. I am trying to make a master table that joins all tables and columns together in one big table. I'm having trouble woth the sql query for this i keep getting stuck with errors. Do anybody have any advice please. What I want is all tables and columns in one table so I can use it for machine learning. Am I doing the right thing here?

Answer 1

All the *insights tables, as well as historic_runners are indexed by race_id and runner_id.  So these will be easy to join, although some columns are repeated, so it's necessary to reference those columns differently, or to remove them from your query.

The historic_races table references races only, so a JOIN will be needed to repeat any particular race information you're interested in.

In general, however, it's usually a good principal to start small and expand to remove errors.  So try and join, say, all fields from historic_runners_insights with some key fields from daily_trainers_insights.  There is enough in there for a good machine learning model.  Then you can expand afterwards.

Answer 2

PS:  Here's an example if you do want to create a master table - it doesn't include all fields, but provides a template you can use to easily add new fields:

CREATE TABLE smartform_master AS

SELECT

    -- Base runner info

    hrb.runner_id,

    hrb.race_id,

    hrb.name AS runner_name,

    hrb.age AS runner_age,

    hrb.trainer_id,

    hrb.jockey_id,

    hrb.starting_price_decimal,

    hrb.finish_position,

    -- Race info

    hrc.meeting_date,

    hrc.course AS race_course,

    hrc.going AS race_going,

    hrc.distance_yards,

    hrc.race_type AS race_type_main,

    hrc.class AS race_class,

    -- Historic runner insights

    hri.official_rating AS hri_official_rating,

    hri.weight_pounds AS hri_weight_pounds,

    hri.speed_rating_LTO AS hri_speed_rating_LTO,

    hri.prev_runner_win_strike,

    hri.prev_runner_place_strike,

    hri.percent_beaten_LTO,

    hri.won_LTO,

    -- Trainer insights

    dti.rc_21D_strike_rate AS trainer_21D_strike,

    dti.rc_5Y_strike_rate AS trainer_5Y_strike,

    dti.rt_5Y_course_strike_rate AS trainer_course_strike,

    dti.rt_5Y_distance_strike_rate AS trainer_distance_strike,

    -- Jockey insights

    dji.rc_21D_strike_rate AS jockey_21D_strike,

    dji.rc_5Y_strike_rate AS jockey_5Y_strike,

    dji.rt_5Y_course_strike_rate AS jockey_course_strike,

    dji.rt_5Y_distance_strike_rate AS jockey_distance_strike

FROM historic_runners_beta hrb

LEFT JOIN historic_races_beta hrc

  ON hrb.race_id = hrc.race_id

LEFT JOIN historic_runners_insights hri

  ON hrb.race_id = hri.race_id AND hrb.runner_id = hri.runner_id

LEFT JOIN daily_trainers_insights dti

  ON hrb.race_id = dti.race_id AND hrb.runner_id = dti.runner_id

LEFT JOIN daily_jockeys_insights dji

  ON hrb.race_id = dji.race_id AND hrb.runner_id = dji.runner_id;