MySQL: get records from database and add a COUNT() column to the rows

Question

I'm trying to retrieve books from one table and left join the chapters table. What I need from the second table is just the COUNT() of chapters available for those books and add that value as an extra column called chapters (or something else).

My current try looks like this: SELECT b.*, count(c.chapter_nr) as chapters FROM books as b left join chapters as c on c.book_id = b.id

This only gets one from from the books table and adds the count() result to that row, but I'd like to get ALL the rows from the books table, hence the LEFT JOIN

Answer 1

SELECT b.*, count(c.chapter_nr) as chapters 
FROM books as b 
LEFT JOIN chapters as c on (c.book_id = b.id)
GROUP BY b.id

EXPLANATION

You need to group by the book in order to determine the actual chapter counts. If you were to leave out the GROUP BY clause, you would be retrieving a resultset of all chapters of every book. You simply want to limit the results to unique books and their corresponding chapter counts.

Answer 2

Untested, but you need a "group by" clause to do what you want:

Select b.*, count(*) as chapters
from books b left outer join chapters c
on c.book_id = b.id
group by b.*

Answer 3

You are missing the GROUP BY clause:

SELECT b.*, count(c.chapter_nr) as chapters 
FROM books AS b 
LEFT JOIN chapters AS c ON c.book_id = b.id 
GROUP BY b.id

Answer 4

Try :

SELECT b.*,  
(select count(*) from chapters c where c.book_id = b.id) as chapters   
FROM books b

This will return 0 if there are no chapters for a book.