Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    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;

    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.

    EDIT: I want the recordset be like below;

    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
    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.
    Last edited by demods; 02-15-11 at 10:55.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Code:
    select top 10 * from
    (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) 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 10:40. Reason: Fixed a typo in the code

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    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.
    clearly, the appropriate strategy here is to use two queries

    the first can use TOP, it can also use paging, and retrieve the posts you want

    then the second query can retrieve all the comments for those posts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2009
    Posts
    23
    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.

  5. #5
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by r937 View Post
    clearly, the appropriate strategy here is to use two queries

    the first can use TOP, it can also use paging, and retrieve the posts you want

    then the second query can retrieve all the comments for those posts
    That was what I thought at first but let's say I want to select TOP 10 POSTS and all comments belong to those 10 posts. So doesn't this make;
    • 1 query for TOP N posts
    • 1 query for every post to get comments, which makes 10 queries for 10 posts

    A total of 11 queries? Am I wrong?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    A total of 11 queries? Am I wrong?
    yup, that number should be 2

    1 query to get the 10 posts that you want

    then 1 query to get the comments for those posts
    Code:
    SELECT ...
      FROM comments
     WHERE post_id IN ( 4, 9, 37, ... ) -- list of 10 post ids from 1st query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by r937 View Post
    yup, that number should be 2

    1 query to get the 10 posts that you want

    then 1 query to get the comments for those posts
    Code:
    SELECT ...
      FROM comments
     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?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    1. Query for id's in TOP N posts
    2. Use results from #1 as join criteria to get comments for each of the TOP N posts
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by demods View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2009
    Posts
    23
    Quote Originally Posted by r937 View Post
    are you saying that this is a difficult task for php or asp? because it is actually trivial
    For PHP it's not difficult but for ASP Well, ASP's array support sucks. But I get the point, I will have to find a way to process that array with ASP.

    Thanks r937.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •