Changesets with state count (for Dave)

gistfile1.txt

robert=# \d+ changeset
                                             Table "public.changeset"
 Column |  Type   |                       Modifiers                        | Storage | Stats target | Description 
--------+---------+--------------------------------------------------------+---------+--------------+-------------
 id     | integer | not null default nextval('changeset_id_seq'::regclass) | plain   |              | 
Indexes:
    "changeset_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "state" CONSTRAINT "state_changeset_id_fkey" FOREIGN KEY (changeset_id) REFERENCES changeset(id)
		
robert=# \d+ state
                                                Table "public.state"
    Column    |  Type   |                     Modifiers                      | Storage | Stats target | Description 
--------------+---------+----------------------------------------------------+---------+--------------+-------------
 id           | integer | not null default nextval('state_id_seq'::regclass) | plain   |              | 
 changeset_id | integer | not null                                           | plain   |              | 
Indexes:
    "state_pkey" PRIMARY KEY, btree (id)
    "state_changeset_id_idx" btree (changeset_id)
Foreign-key constraints:
    "state_changeset_id_fkey" FOREIGN KEY (changeset_id) REFERENCES changeset(id)

robert=# SELECT c.*, s.cnt AS state_count
robert-# FROM changeset c
robert-# INNER JOIN (
robert(#   SELECT changeset_id, COUNT(*) AS cnt
robert(#   FROM state
robert(#   GROUP BY changeset_id
robert(#   HAVING COUNT(*) > 0
robert(# ) s ON (c.id = s.changeset_id)