Louis Tran Louis Tran - 1 month ago 7
MySQL Question

LIMIT results on specific table when using LEFT JOIN

Below is my query. I would like to get only 10 posts from post table. However it

LIMIT 10
below doesn't limit the results in posts table but it does on another table.

Can anyone help me to fix the query? I really appreicate your help.

SELECT posts.id , posts.cat_id , posts.school_id , posts.campus_id , posts.status , posts.priority , posts.title , posts.content , posts.phone , posts.email , posts.tags , posts.zip , posts.price_new , posts.price_old , posts.reviewed_by , posts.reviewed_date , posts.updated_date , posts.posted_date , posts.expired_date , posts.ip_address , schools.school_name, campuses.campus_name , meta.meta_key , meta_value , images.img_name
FROM posts LEFT JOIN
meta
ON posts.id = meta.post_id LEFT JOIN
images
ON posts.id = images.post_id LEFT JOIN
schools
ON posts.school_id = schools.id LEFT JOIN
campuses
ON posts.campus_id = campuses.id
ORDER BY posts.updated_date DESC LIMIT 10

Answer

One method is to use a subquery:

SELECT p.id , p.cat_id, p.school_id, p.campus_id , p.status,
       p.priority, p.title, p.content, p.phone, p.email, p.tags, p.zip,  
       p.price_new, p.price_old, p.reviewed_by, p.reviewed_date,
       p.updated_date, p.posted_date, p.expired_date, p.ip_address,
       s.school_name, c.campus_name,
       m.meta_key, m.meta_value, 
       i.img_name 
FROM (SELECT p.*
      FROM posts p
      ORDER BY p.updated_date DESC
      LIMIT 10
     ) p LEFT JOIN
     meta m
     ON p.id = m.post_id LEFT JOIN
     images i
     ON p.id = i.post_id LEFT JOIN
     schools s
     ON p.school_id = s.id LEFT JOIN
     campuses c
     ON p.campus_id = c.id;

Note that the use of table aliases makes the query easier to write, read, and understand.