MySQL Query Help, including a count?
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
WHE开发者_StackOverflow中文版RE blogentry.deleted = 'N'
ORDER BY blogentry.dateAdded DESC
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')
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
精彩评论