Unanswered: 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,
-- 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,
-- 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
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.
EDIT: I want the recordset be like below;
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
If I SELECT TOP 2 POSTS, only those two posts and their comments will be selected, which COMMENT_IDs are 1, 2 and 5. But if I select top 3 posts, then those 3 posts with comment 1, 2, 3, 4 and 5 will be selected. I don't want to break the table.
select top 10 * from
(SELECT POST_ID, NULL AS COMMENT_ID, USER_ID, POST, CREATED, POST_ID AS SEQUENCE FROM POSTS
SELECT POST_ID, COMMENT_ID, USER_ID, COMMENT AS POST, CREATED, POST_ID AS SEQUENCE FROM COMMENTS) a
ORDER BY SEQUENCE DESC
Of course, it would probably be easier to combine the two tables. Is there supposed to be some sort of parent child relationship between comments and posts?
Last edited by MCrowley; 02-15-11 at 09:40.
Reason: Fixed a typo in the code
MCrowley, your query is selecting only TOP N records but COMMENTS are broken if you select, say, TOP 4. I want to select TOP N records and ALL COMMENTS belong to those posts. I edited my question and added the desired recordset.
WHERE post_id IN ( 4, 9, 37, ... ) -- list of 10 post ids from 1st query
But then how will I display POSTS and their own COMMENTS only, like Facebook or any other social network website?
If I do like you say; I first get the POSTS records and start to display each one. After displaying the first POST, I have to display its COMMENTS below that post. Then the next post and its comments will come, and so on. So you say that this is PHP's or ASP's job but not DB's?
Is my desired recordset a wrong approach for a social networking site?
So you say that this is PHP's or ASP's job but not DB's?
it is, yes, if you retrieved 2 result sets
but i mean, hey, you have two arrays, with appropriate data columns that can be used to associate rows in these arrays, are you saying that this is a difficult task for php or asp? because it is actually trivial