SELECT TOP (@Posts) p.postID,
ISNULL(p.cID, 0) AS 'cID',
p.Header,
p.Text, p.bID,
ISNULL(c.Name, 'Saknas') AS 'categoryName',
CONVERT(VARCHAR(17), p.Date, 113) AS date,
ISNULL(u.UserName, 'Avregistrerad') AS 'UserName',
COUNT(com.comID) AS numberOfComment
FROM post AS p
LEFT OUTER JOIN category AS c
ON p.cID = c.cID
LEFT OUTER JOIN aspnet_Users AS u
ON p.UserId = u.UserId
LEFT OUTER JOIN comment AS com
ON com.postID = p.postID
WHERE p.bID = @bloggID
AND p.Status = 1
ORDER BY p.Date DESC
Får felmeddelandet:
Column 'post.postID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Det känns fel med en join där för att bara få fram antalet kommentarer.
En subselect skulle passa bättre!
Sen behöver du inte ha apostrofer på dina aliasnamn.
SELECT TOP (@Posts) p.postID,
ISNULL(p.cID, 0) AS cID,
p.Header,
p.Text, p.bID,
ISNULL(c.Name, 'Saknas') AS categoryName,
CONVERT(VARCHAR(17), p.Date, 113) AS date,
ISNULL(u.UserName, 'Avregistrerad') AS UserName,
(SELECT COUNT(*) FROM comment AS com WHERE p.postID = com.postID) AS numberOfComment
FROM post AS p
LEFT OUTER JOIN category AS c ON p.cID = c.cID
LEFT OUTER JOIN aspnet_Users AS u ON p.UserId = u.UserId
WHERE p.bID = @bloggID
AND p.Status = 1
ORDER BY p.Date DESC
