SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LEFT JOIN and LIMIT only 1 table

    I would like to display on the same page last 10 posts and also all comments for each post. What would be the best way to make this? The problem with LEFT JOIN is it limits 10 posts and comments together.

    wall_posts
    postId | post

    post_comments
    commentId | comment | postId

    So this is not ok because it can shows only 1 post and 9 comments:
    PHP Code:
    SELECT post,comment FROM wall_posts
    LEFT JOIN wall_posts ON post_comments
    .postId=wall_posts.postId
    LIMIT 10 
    Do I actually need to use 10 queries?

    Thank you
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    This should work (not tested) :

    Code MySQL:
    SELECT post
         , comment
      FROM (
         SELECT post
           FROM wall_posts
          LIMIT 10
      ) AS wall_posts
      LEFT
      JOIN wall_posts
        ON post_comments.postId=wall_posts.postId
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    LIMIT without ORDER BY makes no sense

    also, some versions of mysql don't support LIMIT inside a subquery

    my advice for this scenario is two queries -- one to get the last 10 posts, and the second, using an IN list of post ids from the first query, to pick up all related comments

    then the application language can process the two results in tandem
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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