Reputation: 43
Setup as below works.
But, what I want to achive is to count how many rows there is in BlogComment
where BlogComment.BlogPostId = BlogPost.BlogPostId
.
Anyone got suggestion ?
SELECT * FROM BlogPost
INNER JOIN BlogUser
ON BlogPost.BlogUserId = BlogUser.BlogUserId
INNER JOIN Category
ON BlogPost.CategoryId = Category.CategoryId
INNER JOIN BlogComment
ON BlogPost.BlogPostId = BlogComment.BlogPostId
Order By BlogPost.BlogPostId Desc
Upvotes: 1
Views: 462
Reputation: 52675
If you want all the data plus a count (as opposed to just the count) you can create an inline query in the from clause to do this.
SELECT *,
COALESCE(c.count_post_id,0) as count_post_id
FROM blogpost
INNER JOIN bloguser
ON blogpost.bloguserid = bloguser.bloguserid
INNER JOIN category
ON blogpost.categoryid = category.categoryid
LEFT JOIN (SELECT blogpostid,
COUNT(blogpostid) count_post_id
FROM blogcomment
GROUP BY blogpostid) c
ON c.blogpostid = blogpost.blogpostid
ORDER BY blogpost.blogpostid DESC
Some RDMS give additional options like using CTEs or Cross Apply
Upvotes: 1
Reputation: 783
Is it how many comments each blogpost have you want? then it should be
SELECT blogpostid,
COUNT(*) as TotalComments
FROM blogpost p
JOIN blogcomment c
ON p.blogpostid = c.blogpostid
GROUP BY blogpostid;
Upvotes: 1
Reputation: 12554
SELECT COUNT(*)
FROM blogpost a
WHERE EXISTS (SELECT *
FROM blogcomment
WHERE blogcomment.blogpostid = a.blogpostid)
Upvotes: 0
Reputation: 26871
try with:
SELECT BlogPost.*, BlogUser.*, Category.*, BlogComment.*, COUNT(BlogComment.BlogCommentId) FROM BlogPost
INNER JOIN BlogUser
ON BlogPost.BlogUserId = BlogUser.BlogUserId
INNER JOIN Category
ON BlogPost.CategoryId = Category.CategoryId
INNER JOIN BlogComment
ON BlogPost.BlogPostId = BlogComment.BlogPostId
GROUP BY BlogPost.BlogPostId Order By BlogPost.BlogPostId Desc
Upvotes: 0