mySQL joins or subquery

I have three tables: show, episode, and airing. Each show has multiple episodes, and each episode has multiple airings. I want one row for each show where the airing.start_time is closest start_time to right now (but in the future). If there isn't a future start_time, then I want the row anyway (start_time should be empty).

Here's my current query:

    SELECT s.project_id,
           s.title, 
           s.description, 
           s.topic, 
           s.status, 
           a.start_time, 
           a.channel 
      FROM show s 
 LEFT JOIN episode e ON s.project_id = e.project_id 
 LEFT JOIN airing a ON e.episode_id = a.episode_id 
     WHERE s.status = "Active" AND s.title LIKE "a%" AND a.start_time > NOW()
  ORDER BY s.开发者_StackOverflow社区title ASC, a.start_time ASC
  GROUP BY s.project_id

I'm not sure where to go next. Any help?

Without showing airing.channel :

    SELECT s.project_id,
           s.title, 
           s.description, 
           s.topic, 
           s.status, 
           MIN(a.start_time) 
      FROM show s 
 LEFT JOIN episode e ON s.project_id = e.project_id 
 LEFT JOIN airing a ON e.episode_id = a.episode_id 
                    AND a.start_time > NOW()
     WHERE s.status = "Active" AND s.title LIKE "a%"
  GROUP BY s.project_id
  ORDER BY s.title ASC

Showing channel too:

    SELECT s.project_id,
           s.title, 
           s.description, 
           s.topic, 
           s.status, 
             ( SELECT a.start_time
                 FROM airing AS a 
                 JOIN episode AS e ON e.episode_id = a.episode_id 
                WHERE s.project_id = e.project_id 
                  AND a.start_time > NOW() 
             ORDER BY a.start_time
                LIMIT 1    
             )
           AS start_time,
             ( SELECT a.channel
                 FROM airing AS a 
                 JOIN episode AS e ON e.episode_id = a.episode_id 
                WHERE s.project_id = e.project_id 
                  AND a.start_time > NOW() 
             ORDER BY a.start_time
                LIMIT 1    
             )
           AS channel
      FROM show s     
     WHERE s.status = "Active" AND s.title LIKE "a%"
  ORDER BY s.title ASC

or:

    SELECT s.project_id,
           s.title, 
           s.description, 
           s.topic, 
           s.status, 
           a.start_time,
           a.channel   
      FROM show s     
 LEFT JOIN airing a ON a.airing_id =
                     ( SELECT a.airing_id
                         FROM airing AS a 
                         JOIN episode AS e ON e.episode_id = a.episode_id 
                        WHERE s.project_id = e.project_id 
                          AND a.start_time > NOW() 
                     ORDER BY a.start_time
                        LIMIT 1    
                     )   
     WHERE s.status = "Active" AND s.title LIKE "a%"
  ORDER BY s.title ASC

If they are slow, tell us what indexes you have on the tables and what types the fields are.

To handle the no future start time, change your time check to

... AND ((a.start_time > NOW()) or (a.start_time is null))

assuming that "empty" you mention is a null. However, if you don't clear start_times for previous episodes, then this won't work, and you should just remove the start_time check entirely. There's no point in checking for past_start times, because you be doing (x > y or x < y), which would only fail if (x = y).

This query sounds like what you want, but channel will also be null if there isn't a future start_time

SELECT s.project_id,
       s.title, 
       s.description, 
       s.topic, 
       s.status, 
       a.start_time, 
       a.channel 
FROM show s 
LEFT JOIN episode e ON s.project_id = e.project_id 
LEFT JOIN (SELECT start_time, channel, episode_id FROM airing a1
           WHERE a1.start_time = (SELECT MIN(a2.start_time) FROM airing a2
                                  WHERE a2.start_time > NOW() AND
                                        a2.airing_id = a1.airing_id)
          ) a ON e.episode_id = a.episode_id 
WHERE s.status = 'Active' AND s.title LIKE 'a%'
ORDER BY s.title ASC, a.start_time ASC
GROUP BY s.project_id

Edit as per ypercube's comment:

SELECT s.project_id,
       s.title, 
       s.description, 
       s.topic, 
       s.status, 
       a.start_time, 
       a.channel 
FROM show s 
LEFT JOIN (SELECT a1.start_time, a1.channel, e.project_id
           FROM airing a1
                JOIN episode e ON a1.episode_id = e.episode_id
           WHERE a1.start_time = (SELECT MIN(a2.start_time) FROM airing a2
                                  WHERE a2.start_time > NOW() AND
                                        a2.airing_id = a1.airing_id)
          ) a ON s.project_id = a.project_id 
WHERE s.status = 'Active' AND s.title LIKE 'a%'
ORDER BY s.title ASC, a.start_time ASC
GROUP BY s.project_id

Try this:

SELECT s.project_id,
       s.title, 
       s.description, 
       s.topic, 
       s.status, 
       a.start_time, 
       a.channel 
FROM show s 
LEFT JOIN episode e ON s.project_id = e.project_id 
LEFT JOIN airing a ON e.episode_id = a.episode_id 
WHERE s.status = "Active" AND s.title LIKE "a%" AND a.start_time > NOW()
-- Here's the new bit:
AND a.start_time = (SELECT start_time from airing where episode_id = e.episode_id order by abs(now() - start_time) limit 1)
ORDER BY s.title ASC, a.start_time ASC
GROUP BY s.project_id

This picks the airing that is closest to "now" by doing a subselect ording by distance in time from now limit 1 (to pick the lowest differenced one)