CODEWITHSUNDEEP
sqlsql-serverpagination
Burak Erdem
Burak Erdem

Reputation: 19969

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            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

Upvotes: 1

Views: 1954

Answers (5)

Anil Kondi
Anil Kondi

Reputation: 1

Join two tables and retrieve top 10 records

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

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

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

Upvotes: 4

Tsabo
Tsabo

Reputation: 844

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.

Upvotes: 0

Ciaran Archer
Ciaran Archer

Reputation: 12446

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.

Upvotes: 0

user
user

Reputation: 6947

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.

Upvotes: 0

Related Questions