Home / Tricky Select Queries / Question No: 92

I have a child table named author_books where I store books against each author. So, I want a query to find the books which have authors with more than one book in this table. The structure of the table is as below:

CREATE TABLE `author_books` (
`ab_id` INT NOT NULL ,
`ab_author_id` MEDIUMINT NOT NULL ,
`ab_book_id` BIGINT NOT NULL
) ENGINE = MYISAM; 

This is can be done through creating drive table of author_books table and then joining the both. First part of the query will find the authors with multiple books. Then join the drived table to that on ab_author_id. Take the example below:

SELECT ab_1.ab_author_id, ab_1.ab_book_id
FROM author_books ab_1
INNER JOIN
(

     SELECT ab_author_id, COUNT( ab_book_id )
     FROM author_books ab_2
     GROUP BY ab_author_id
     HAVING COUNT( ab_book_id ) >1

) AS ab_3 ON ab_1.ab_author_id = ab_3.ab_author_id;