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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy