sql query many-to-many

Question

I have three tables: authors (author_id, author_name), books (book_id, book_name), author_book (author_id, book_id). I want to find all authors who wrote more than two book. How can I do that?

Answer 1

I think this is what you're looking for

select a.author_name, count(ab.book_id) total_books
from author_book ab
join authors a on a.author_id = ab.author_id
group by ab.author_name
having count(ab.book_id) > 2