SELECT
    relation_class.*,
    pp_link_base_setting.pp_scheme,
    pp_link_base_setting.attendance_rate_calc,
    pp_link_base_setting.in_on_time_rate,
    eval_dates.id AS trz_evaluation_date_id,
    eval_dates.start_date,
    eval_dates.end_date,
    touenIndex.mst_numerical_index_id,
    touenIndex.name AS mst_numerical_index_name,
    touenIndex.viewpoint AS mst_numerical_index_viewpoint,
    touenIndex.rule AS mst_numerical_index_rule,
    touenIndex.unit AS mst_numerical_index_unit,
    touenIndex.aggregate_pattern AS mst_numerical_index_aggregate_pattern,
    touenIndex.zero_value AS mst_numerical_index_zero_value,
    touenIndex.zero_value_condition AS mst_numerical_index_zero_value_condition,
    touenIndex.perfect_value AS mst_numerical_index_perfect_value,
    touenIndex.perfect_value_condition AS mst_numerical_index_perfect_value_condition,
    touenIndex.point AS mst_numerical_index_point,
    touenIndex.note AS mst_numerical_index_note,
    touenIndex.score AS allocation_of_marks,
    touenIndex.pp_relation_item,
    touenIndex.link_period,
    touenIndex.link_period_start_month
FROM
    relation_class
    JOIN
        pp_link_base_setting
    ON  relation_class.facility_id = pp_link_base_setting.facility_id
    AND relation_class.hoikuen_id = pp_link_base_setting.hoikuen_id
    JOIN
        (
            SELECT
                eval1.*
            FROM
                trz_evaluation_dates AS eval1
                LEFT JOIN
                    trz_evaluation_dates AS eval2
                ON  (
                        eval1.facility_id = eval2.facility_id
                    AND eval1.id < eval2.id
                    )
            WHERE
                eval2.id IS NULL
        ) AS eval_dates
    ON  relation_class.facility_id = eval_dates.facility_id
    JOIN
        pp_class_info
    ON  relation_class.hoikuen_id = pp_class_info.hoikuen_id
    AND relation_class.classroom_id = pp_class_info.classroom_id
    JOIN
        trz_teams
    ON  relation_class.trz_team_id = trz_teams.id
    JOIN
        mst_numerical_index_groups
    ON  trz_teams.mst_numerical_index_group_id = mst_numerical_index_groups.id
    JOIN
        (
            SELECT
                mst_numerical_index_group_lists.id,
                mst_numerical_index_group_lists.mst_numerical_index_group_id,
                mst_numerical_index_group_lists.mst_numerical_index_id,
                mst_numerical_index_group_lists.score,
                mst_numerical_indexs.link_period,
                mst_numerical_indexs.link_period_start_month,
                mst_numerical_indexs.name,
                mst_numerical_indexs.viewpoint,
                mst_numerical_indexs.rule,
                mst_numerical_indexs.unit,
                mst_numerical_indexs.aggregate_pattern,
                mst_numerical_indexs.zero_value,
                mst_numerical_indexs.zero_value_condition,
                mst_numerical_indexs.perfect_value,
                mst_numerical_indexs.perfect_value_condition,
                mst_numerical_indexs.point,
                mst_numerical_indexs.note,
                mst_numerical_indexs.pp_relation_item
            FROM
                mst_numerical_index_group_lists
                JOIN
                    mst_numerical_indexs
                ON  mst_numerical_indexs.id = mst_numerical_index_group_lists.mst_numerical_index_id
            WHERE
                mst_numerical_indexs.pp_relation_item in (%s)
            AND mst_numerical_indexs.flg_delete != 1
        ) AS touenIndex
    ON  trz_teams.mst_numerical_index_group_id = touenIndex.mst_numerical_index_group_id
WHERE
    pp_link_base_setting.pp_link_flg = 1
AND pp_link_base_setting.flg_delete != 1
AND trz_teams.flg_delete != 1
AND relation_class.classroom_id != -1;