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)