check all data from a SQL table dependent upon another two tables

[Atanu Barai]

I have three table in database for a simple blog.

1st(userdata) is for userID userName, profession, birthdate & other personal information.

2nd(postdata) is for postID, userID postBody, postTitle, postDate

3rd(commentdata)is for commentID, postID, commentBody.

now I want to bind these data in a asp.net datalist so that for TOP 10 post userName, postTitle, postBody and number of comments for each post is shown. How can design the sql command. I tried

SELECT TOP 10 userdata.name, postid, postdata.uid, postdata.title, postdata.body, postdata.postdate"
+ " FROM postdata INNER JOIN userdata ON postdata.uid = userdata.uid ORDER BY postdata.postdate DESC

Now how can I attach "postdata" table with this command
to get COUNT(postid)

[nathj]

Hi,

I would try something like:

SELECT
a.name, b.postid, b..uid, b.title, b.body, b.postdate FROM postdata b
LEFT JOIN userdata a ON b.uid = a.uid
ORDER BY b.postdate DESC
WHERE row >= 1 AND row <= 10

That SQL should sort you out. It's a shame SQL Server hasn't implemented LIMIT like in MySQL

Cheers
nathj