SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Limiting on 1 table in a join case?

    I'm currently joining 2 tables, and am having a slight problem. Basically, the table that links the posts and hte categories should be able to have as many categories as needed (realistically it's 2, occasionally 3) per post. Now the problem is, when I want to select 10 posts, I can't just LIMIT 10, because if there's 5 posts with 2 categories each, it'll come out as 10. (Oh, and the query's something like "SELECT posts.*, postcats.cid FROM posts, postcats WHERE posts.id=postcats.pid && ..."

    Any ideas what the most efficient way of doing this is? Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    could you please be a bit more specific about what "doing this" means?

    to limit the number of rows returned, one does indeed use LIMIT

    what version of mysql are you on? you might want to use GROUP_CONCAT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    4.0.22. Basically, I need to know if there's an efficient way to limit the number of results returend, while still leaving the number of entries in PostCats to infinite... so I want to limit 10 unique entries from the posts table. Hopefully that made sense.

    Basically I'm going SELECT posts.*, postcats.cid FROM posts, postcats WHERE postcats.pid=posts.id (Actually I'm using a join, but either would work, eh?). Some posts have more than 1 entry for postcats. I need 10 unique entries from posts, while not limiting the number of categories selected (as if I say LIMIT 10 on that, and 5 posts have 2 categories each, it only returns the 5 posts). Thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    since you are not on the current release, you will just have to run two queries

    one to get your 10 posts, and then another to get their categories using an IN list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay. 4.1 has the new feature? I have a dedicated server, I could try and upgrade if it's worth it.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select top10.*
         , postcats.cid 
      from (
           select *
             from posts
           order
               by something
           limit 10
           ) as top10      
    inner
      join postcats 
        on top10.id  
         = postcats.pid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard silver trophy someonewhois's Avatar
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    6,364
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dude that's awesome. 4.1 only, eh? Thanks though, I'll try and upgrade and implement that.


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
  •