How do you restrict compound records in MYSQL?

Question

I have been working with MYSQL for several years, and I often wonder about this problem, but still have not reached the research point. So here. Say I have a table of authors and a table of books. Each book has a field for links to the author.

Now let me say that I want to get the author and his book, all in one set.

SELECT * FROM authors, books WHERE books.author = authors.id

There are no problems so far. Now for the difficult part: how to get, say, 5 authors and his books?

if:

SELECT * FROM authors, books WHERE books.author = authors.id LIMIT 5

I just get to get the first 5 books related to one author.

Is this clear enough? Can this be done in MYSQL, or do I need to resort to this in the code (which I do not want to do)?

Thank you so much!

Answer 1

:

SELECT *
FROM (SELECT * FROM authors LIMIT 5) AS authors, books
WHERE books.author = authors.id

FROM : http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html