CODEWITHSUNDEEP
sql
miker
miker

Reputation: 43

SQL Inner join and count

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

Answers (4)

Conrad Frix
Conrad Frix

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

Mikael Sundberg
Mikael Sundberg

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

Alexander Galkin
Alexander Galkin

Reputation: 12554

SELECT COUNT(*) 
FROM   blogpost a 
WHERE  EXISTS (SELECT * 
               FROM   blogcomment 
               WHERE  blogcomment.blogpostid = a.blogpostid)

Upvotes: 0

Tudor Constantin
Tudor Constantin

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

Related Questions