How do you limit compound records in MYSQL?

Question

I've been working with MYSQL for a few years now, and I often find myself wondering about this problem, but have never gotten around to researching it until now. So here goes. Let's say I have a table of authors, and a table of books. Each book has a field to link it to an author.

Now let's say I want to get an author and his books, all in one set.

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

No problem so far. Now for the tricky part: how do I get, say, 5 authors AND his books?

if I do:

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

I'll just wind up getting the first 5 books linked to a single author.

Is that clear enough? Is this possible to do in MYSQL, or do I have to resort to doing it in code (which I don't want to do)?

Thanks much!

Answer 1

Use a subquery:

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

Read more about subqueries in the FROM clause here: http://dev.mysql.com/doc/refman/5.0/en/unnamed-views.html