Background
==========

I have a Postgres database of shows (show\_id int, title string) and episodes (show\_id int, ep\_id int, title string, date int).

I want to issue a query that returns all shows with their titles, their episode count (= number of episodes with their id as the show\_id), next episode title, and next episode airtime. The times are Unix timestamps and the next episode is obviously `min(date) where date > now`. If a show has no upcoming episodes, those fields are null.

Schema Example
==============

shows:

```
show_id | title
--------|-------
1       | The X-Files
2       | Fringe
3       | Seinfeld

```

episodes:

```
ep_id | title             | date      | show_id
------|-------------------|-----------|---------
1     | Mulder's Holiday  | 110876621 | 1
2     | Walt Retires      | 128101332 | 2
3     | Kramer & Dracula  | 184220112 | 3
4     | Scully's Tattoo   | 100298110 | 1
5     | Skinner in Hawaii | 218101310 | 1

```

Desired result set
==================

```
show_title  | episode_count | next_episode      | next_airing
------------|---------------|-------------------|-----------
The X-Files | 221           | Skinner in Hawaii | 218101310
Seinfeld    | 167           | NULL              | NULL

```

What I've tried
===============

```
select title, c.count, d.next_episode, d.next_airing
from shows s
inner join (select show_id, count(*)
            from episodes
            group by show_id) c
inner join (select show_id, title next_next_episode, date next_airing
            from episodes
            group by show_id) d
on c.show_id = s.show_id
on d.show_id = s.show_id

```

The first inner join works, to select episode counts, but duplicating it to select the next episode title and date doesn't. The problem seems to be that inner joins on subselects only work if what you're selecting is part of an aggregate function? Or something along those lines -- my book doesn't cover this, and I found it hard to figure out on my own, after spending half my evening on Google. If anyone could offer a tip about how to do this I'd really be grateful.