May 7, 2007 at 12:39 am
I have a join query.
I am trying to get the count from the comment table for the corresponding post_id.
So the data that I would like to get back is: post_id, UserName, post_name, post_date,post_views, #of comments for post
Here is the query that I have so far:
SELECT post.post_id, aspnet_Users.UserName, post.post_name,
post.post_date, post.post_views --count(comment.comment)
FROM post
INNER JOIN aspnet_Users
ON post.userid = aspnet_Users.UserId
left outer JOIN comment
ON post.post_id = comment.post_id
ORDER BY post.post_date DESC
Your help is much appreciated.
Norbert
May 7, 2007 at 7:51 am
There are several ways to perform the query, the simplist would probably be:
SELECT post.post_id, aspnet_Users.UserName, post.post_name,
post.post_date, post.post_views, (select count(comment.comment) from comment where comment.post_id = post.post_id) as Total_Comments
FROM post INNER JOIN aspnet_Users ON (post.userid = aspnet_Users.UserId)
ORDER BY post.post_date DESC
You could do it with a join on the Comment table along with a group by clause but I think the above is easier.
HTH,
James.
May 7, 2007 at 10:01 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply