Limiting on 1 table in a join case?

[someonewhois]

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.

[r937]

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

[someonewhois]

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!

[r937]

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

[someonewhois]

Okay. 4.1 has the new feature? I have a dedicated server, I could try and upgrade if it's worth it.

[r937]

select top10.*
     , postcats.cid 
  from (
       select *
         from posts
       order
           by something
       limit 10
       ) as top10      
inner
  join postcats 
    on top10.id  
     = postcats.pid

[someonewhois]

Dude that's awesome. 4.1 only, eh? Thanks though, I'll try and upgrade and implement that.