CREATE OR REPLACE VIEW study.timelinesVsSites
AS
SELECT
    t_line.timelineID
    ,site.siteID
    ,t_line.timelineName
    ,t_line.timelineNotes
    ,tline_v_site.isDeactivated
    ,t_line_stage.timelineStageID
    ,t_line_stage.stageName
    ,t_line_stage.stagePredecessorID
    ,predecessor.stageName AS predecessorName
    ,t_line_stage.timelineStageNotes
FROM study.timeline AS t_line
    -- This has to be a left join as potentially someone might create a timeline
    -- but not assign a stage to it which would cause this join to not return timelines.
    LEFT JOIN study.timelineStage AS t_line_stage
        ON t_line.timelineID = t_line_stage.timelineID
    INNER JOIN study.timelineVsSite AS tline_v_site
        ON tline_v_site.timelineID = t_line.timelineID
    -- join table on itself to get predecessor stage names.
    LEFT JOIN study.timelineStage AS predecessor
        ON t_line_stage.stagePredecessorID = predecessor.stagePredecessorID
    -- join on customer.site to translate siteID = 0 into all known sites
    LEFT JOIN customer.site
        ON site.siteID = if(tline_v_site.siteID = 0, site.siteID, tline_v_site.siteID)
ORDER BY timelineID, SiteID, timelineStageID;