MySQL Cross-aggregates

-- ---------------------------------------------------------------------------
-- Cross-aggregates

-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/queries.php#1124
-- ---------------------------------------------------------------------------

-- Given the table authorbook(authid INT, bookid INT), what query finds the
-- books who have authors with more than one book in the table?

-- Even one level of recursion can induce a mild trance. Escape the trance
-- by taking the problem one step at a time. First write the query that
-- finds the authors with multiple books. Then join an outer query to that
-- on authorid, and have the outer query select bookid:

SELECT
  a1.bookid
FROM
  authorbook a1
INNER JOIN (
  SELECT authid, count(bookid)
  FROM
    authorbook a2
  GROUP BY
    authid
  HAVING
    COUNT(bookid) > 1
) AS a3 ON a1.authid = a3.authid;