A table with blog posts that are in one or more categories. Now I want to select 10 posts and having the categories for each of the posts. That means inner joining each post on the post-to-category table etc.
Problem: limit 10 gives only 10 rows (eg one post with 10 categories). I want to have 10 different posts with all categories for each post (eg if each post would have 2 categories it should return 20 rows).
You need to post your table definitions for folks to be much help... but assuming you have a table "story" with a "story_id" field as it's primary key, and a table categories with a foreign key to story id you could do this:
select s.*,c.*
from story s
left outer join categories c
on c.story_id=s.story_id
where story_id in (select story_id from story order by pub_date desc limit 10)
The subselect would pull the 10 most recent story ids then the main select does the outside join.