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.

Solution

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.

Comments

[Manux22]

i try what you say, but need set the limit to "0,3" to display the first post and "0,4" for the second, if i set the limit in 2 or 1, the query returned empty

[Yigitalp Ertem]

What is that you exactly expect from the query? "10 posts that both has comments and relations", or "10 posts with or without comments and relations", "10 posts that have at least 1 comment"... Maybe that explanation may help.

[Manux22]

I need 10 posts with all comments from this. This code not work for my because the subquery take the 10 first posts, but all this posts not validate "type_rel=1" condition in the last "INNER". I need 10 (variable) posts that meet all conditions. I need it to paginate all posts that meet the conditions

[Taryn]

Comments are not for extended discussion; this conversation has been moved to chat.