select - mySQL joins or subquery -

select - mySQL joins or subquery -

i have 3 tables: show, episode, , airing. each show has multiple episodes, , each episode has multiple airings. want 1 row each show airing.start_time closest start_time right (but in future). if there isn't future start_time, want row anyway (start_time should empty).

here's current query:

select s.project_id, s.title, s.description, s.topic, s.status, a.start_time, a.channel show s left bring together episode e on s.project_id = e.project_id left bring together airing on e.episode_id = a.episode_id s.status = "active" , s.title "a%" , a.start_time > now() order s.title asc, a.start_time asc grouping s.project_id

i'm not sure go next. help?

without showing airing.channel :

select s.project_id, s.title, s.description, s.topic, s.status, min(a.start_time) show s left bring together episode e on s.project_id = e.project_id left bring together airing on e.episode_id = a.episode_id , a.start_time > now() s.status = "active" , s.title "a%" grouping s.project_id order s.title asc

showing channel too:

select s.project_id, s.title, s.description, s.topic, s.status, ( select a.start_time airing bring together episode e on e.episode_id = a.episode_id s.project_id = e.project_id , a.start_time > now() order a.start_time limit 1 ) start_time, ( select a.channel airing bring together episode e on e.episode_id = a.episode_id s.project_id = e.project_id , a.start_time > now() order a.start_time limit 1 ) channel show s s.status = "active" , s.title "a%" order s.title asc

or:

select s.project_id, s.title, s.description, s.topic, s.status, a.start_time, a.channel show s left bring together airing on a.airing_id = ( select a.airing_id airing bring together episode e on e.episode_id = a.episode_id s.project_id = e.project_id , a.start_time > now() order a.start_time limit 1 ) s.status = "active" , s.title "a%" order s.title asc

if slow, tell indexes have on tables , types fields are.

mysql select join