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;
Code:
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
Code:
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.