MySQL Query Help, including a count?

Question

Any ideas why this isn't working in MySQL?

SELECT blogentry.*, 
       person.personName, 
       (SELECT * 
          FROM BlogEntryComment 
         Where BlogEntryID = '8') as CommentCount    
  FROM blogentry 
 INNER JOIN person ON blogentry.personID = person.personID
 WHERE blogentry.deleted = 'N'
ORDER BY blogentry.dateAdded DESC

Answer 1

The subquery needs to return only one value: the field count. * returns all rows, whereas count(*) will return how many there are.

(SELECT count(*) FROM BlogEntryComment Where BlogEntryID = '8')

Answer 2

You have to use the aggregate function COUNT in order to get the value - SELECT * in a subSELECT will frail, because it is attempting to return all the column values for a row into a single column.

That said, what you have will return the same CommentCount value for every BLOGENTRY record returned. The following is a better approach:

   SELECT be.*,
          p.personname,
          COALESCE(x.num, 0) AS CommentCount
     FROM BLOGENTRY be
     JOIN PERSON p ON p.personid = be.personid
LEFT JOIN (SELECT bec.blogentryid,
                  COUNT(*) AS num
             FROM BLOGENTRYCOMMENT bec
         GROUP BY bec.blogentryid) x ON x.blogentryid = be.blogentryid