MySQL join only latest row-CodePudding

I have 3 tables with following column names:

restaurants:

id, name, cuisine, address, image_file

reviews:

id, restaurant_id(foreign key), user_id(foreign key), header, content, posted_at, modified_at

users: -id, username, email, password, privileges

In my PHP project on the main page where i want to display all the restaurants with the latest review if there is any review. Can someone please help me how to do that within single query and single foreach loop? I've tried left join with subqueries but could not resolve the problem of duplicated rows. Thanks in advance!

CodePudding user response：

Try this

SELECT *
  FROM restaurants
  LEFT JOIN
     (
       SELECT restaurant_id AS restId, MAX(posted_at) AS lastTime
         FROM reviews
        GROUP BY restaurant_id
     ) AS last_reviews
    ON last_reviews.restId = restaurants.id
  LEFT JOIN reviews
    ON reviews.posted_at = last_reviews.lastTime
   AND reviews.restaurant_id = restaurants.id