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

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 ro开发者_如何学Cw, but I'd like to get ALL the rows from the books table, hence the LEFT JOIN

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.

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

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.

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.*