Unioning Two Tables and Selection Only Top 'n' Records

I'm trying to write a social networking application but I'm stuck with the wall posts/comments part. I have two tables, POSTS and COMMENTS;

CREATE TABLE [dbo].[COMMENTS](
    [COMMENT_ID] [int] IDENTITY(1,1) NOT NULL,
    [POST_ID] [int] NOT NULL,
    [USER_ID] [smallint] NOT NULL,
    [COMMENT] [nvarchar](max) NOT NULL,
    [CREATED] [datetime]
)
-- COMMENT_ID is PK

CREATE TABLE [dbo].[POSTS](
    [POST_ID] [int] IDENTITY(1,1) NOT NULL,
    [USER_ID] [int] NOT NULL,
    [POST] [nvarchar](max) NOT NULL,
    [CREATED] [datetime]
)
-- POST_ID is PK

What I'm trying to do is to select TOP N records from POSTS table and all of comments belong to those records. I'm using the following query but it selects all records from POSTS and COMMENTS, which slows down the server as there are so many POSTS

SELECT POST_ID, NULL AS COMMENT_ID, USER_ID, POST, CREATED, POST_ID AS SEQUENCE FROM POSTS
UNION
SELECT POST_ID, COMMENT_ID, USER_ID, COMMENT AS POST, CREATED, POST_ID AS SEQUENCE FROM COMMENTS
ORDER BY SEQUENCE DESC

How can select TOP 10 POSTS and all comments to those posts? I would also like to dbpage those records, so maybe a paging code would be great for me. I mean instead of selecting top 10 posts, I would like to select 10 POSTS from page 2 and their comments.

I'm not sure if this is possible with this table structure. If it's not, maybe you should offer me a better table structure.

Thanks.

EDIT: I want the recordset be like below. I think the below table is better than INNER JOIN-ed table. All I want is to SELECT TOP N POSTS, nothing more.

POST_ID     | COMMENT_ID    | USER_ID   | POST                          | CREATED
----------------------------------------------------------------------------------------
3               NULL  开发者_如何学JAVA          2           This post has no comments       2011-02-12
1               NULL            1           A new post                      2011-02-11
1               1               2           Comment for post 1              2011-02-11
1               2               1           Another comment for post 1      2011-02-11
1               5               2           Another comment for post 1      2011-02-11
2               NULL            2           Another post                    2011-02-07
2               3               1           Comment for post 2              2011-02-07
2               4               2           Another comment for post 2      2011-02-07

select post_id, comment_id, user_id, post, created
from (
    select top 10 post_id, null as comment_id, user_id, post, created
    from posts
    order by created desc
    ) ss

union

select 
    posts.post_id
    , comment_id
    , comments.user_id
    , comment as post
    , comments.created
from posts
inner join comments on posts.post_id = comments.post_id
where posts.post_id in (
    select top 10 post_id
    from posts
    order by created desc
    )

order by created desc

UNION (and UNION ALL) gives you the combined result of two completely separate SELECT queries, which probably is not quite what you want with that last code snippet.

How can select TOP 10 POSTS and all comments to those posts?

I would imagine that a simple INNER JOIN from POSTS to COMMENTS should be a good start. Not sure how to best implement paging in such a scenario, though.

Regarding pagination, I think you'll need to use ROW_NUMBER() - docs - to create a count based on your POST_ID. That way you can select ranges dynamically at query time.

Hope that helps.

Try this.

SELECT POST_ID, NULL AS COMMENT_ID, USER_ID, POST, CREATED, POST_ID AS SEQUENCE FROM POSTS
UNION
SELECT POST_ID, COMMENT_ID, USER_ID, COMMENT AS POST, CREATED, POST_ID AS SEQUENCE FROM COMMENTS
ORDER BY SEQUENCE DESC
WHERE (POSTS.POST_ID IN (SELECT TOP (10) POST_ID FROM POSTS as POSTS1))

Allso I dont understand why you are using Union over Inner Join. You might want to try it out too.

Join two tables and retrieve top 10 records

select top 10 * from Table1 inner join Table2 on Table1.empid=Table2.empid