select
    t.name as 'Акция',
       t.id as 'Айди родителя',
    tp.name as 'Дочернее название',
       te.id as 'Айди дочернее',
    cw.name as 'Направление',
    if( te.payment='CASH','Наличный','Банк') as 'Оплата',
    (select tpd.included from  tariff_promo_details tpd where tpd.tariff_promo_id=tp.tariff_entry_id and tpd.type='loader') as 'Включено грузчиков',
    (select tpd.additional_price from  tariff_promo_details tpd where tpd.tariff_promo_id=tp.tariff_entry_id and tpd.type='loader') as 'Цена грузчиков',
    (select tpd.additional_price_hour from  tariff_promo_details tpd where tpd.tariff_promo_id=tp.tariff_entry_id and tpd.type='loader') as 'Доп час грузчиков',
    (select tpd.included from  tariff_promo_details tpd where tpd.tariff_promo_id=tp.tariff_entry_id and tpd.type='floor') as 'Включено этажей',
    (select tpd.additional_price from  tariff_promo_details tpd where tpd.tariff_promo_id=tp.tariff_entry_id and tpd.type='floor') as 'Цена этажа',
    te.price as 'Цена',
    te.price_hour as 'Доп час',
    te.price_km as 'цена км',
    te.count_free_km_per_hour as 'Включено км',
    te.price_additional_km as 'Цена доп км',
    t.hint as 'Описание родителя',
    tp.hint as 'Описание дочернее'
from tariff t
         left join city_ways cw on cw.id=t.way_id
         left join city c on c.id=t.city_id
         left join orders_zones oz on oz.id=t.zone_id
         left join tariff_entry te on te.tariff_id=t.id
         left join tariff_promos tp on tp.tariff_entry_id=te.id
where t.type=4 and t.is_delete=0 and is_removed=0 order by Акция,`Дочернее название`,`Включено грузчиков`,Оплата