StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUnioning Two Tables and Selection Only Top 'n' Records
text
Body
copied!<p>I'm trying to write a social networking application but I'm stuck with the wall posts/comments part. I have two tables, <strong>POSTS</strong> and <strong>COMMENTS</strong>;</p> <pre><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 </code></pre> <p>What I'm trying to do is to select <strong>TOP N</strong> records from <strong>POSTS</strong> 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</p> <pre><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 </code></pre> <p>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.</p> <p>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.</p> <p>Thanks.</p> <p><strong>EDIT:</strong> I want the recordset be like below. I think the below table is better than INNER JOIN-ed table. All I want is to <strong>SELECT TOP N POSTS</strong>, nothing more.</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload