MySQL Select with Inner Join, Limit only first

Question

i need paginate a posts from my data base, i write the next query:

SELECT posts.ID, posts.date, comments.name, comments.value 
FROM posts 
INNER JOIN comments 
    ON comments.ID = posts.ID 
INNER JOIN relations 
    ON relations.ID = posts.ID 
WHERE type_rel=1 AND status_post=1 AND 
LIMIT 0,10

The problem is in the LIMIT sentence, i need limit only the "posts" table.

The comments table have many rows and if i put the limit in "0,10", the "posts" table limited to 10 posts, but the comments table also limited to 10.

Someone have a solution to my problem? i use this in PHP query.

Sorry for my bad english, thanks in advance.

Answer 1

You can use a subquery to limit the result set and then join:

SELECT 
  posts.ID, 
  posts.date, 
  comments.name, 
  comments.value 
FROM
  (SELECT * FROM posts WHERE status_post = 1 LIMIT 0,10) posts
LEFT JOIN 
  comments 
  ON comments.ID = posts.ID 
LEFT JOIN 
  relations 
  ON relations.ID = posts.ID AND relations.type_rel = 1

From the comments, the query in your index file is wrong, this is the right one:

SELECT 
  wp_posts.ID, 
  wp_posts.post_date, 
  wp_postmeta.meta_key, 
  wp_postmeta.meta_value 
FROM (SELECT * FROM wp_posts WHERE post_status="publish" AND post_type="post" LIMIT 0,2) wp_posts 
LEFT JOIN wp_postmeta 
  ON wp_postmeta.post_id = wp_posts.ID 
LEFT JOIN wp_term_relationships 
  ON wp_term_relationships.object_id = wp_posts.ID 
  AND wp_term_relationships.term_taxonomy_id=2

Example result here, as you can see you have two posts, id 1 and 5.

If you want to keep the posts which have term_taxonomy_id = 2 use this:

SELECT 
  wp_posts.ID, 
  wp_posts.post_date, 
  wp_postmeta.meta_key, 
  wp_postmeta.meta_value 
FROM (
  SELECT * 
  FROM wp_posts 
  JOIN wp_term_relationships 
  ON wp_term_relationships.object_id = wp_posts.ID 
  AND wp_term_relationships.term_taxonomy_id = 2
  WHERE post_status="publish" AND post_type="post" LIMIT 0,2) wp_posts 
LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID

Example here, post id is 5 and 7, with limit 0,1 returns only 5.