-

SELECT nid, title, date(from_unixtime(d.field_publish_date_value)), f.field_show_target_id from node n
left join `field_data_field_show` f
  on n.vid = f.revision_id and f.entity_type = 'node'
inner join field_data_field_publish_date d
  on n.vid = d.revision_id and d.entity_type = 'node'
where n.type = 'theplatform_video'
  and f.field_show_target_id is not null
order by d.field_publish_date_value desc
limit 100;