help with JOIN in mysql

Question

How in MySQL, I want to get the results from the post table. I do this perfectly. However I would like to get the individual Likes from the like table and the comments from the Comments table.

How could I do this using the JOIN function? Each of the tables have the postID in common (id for the table Posts).

Answer 1

Don't use a join, get the posts, then for each post, get the likes & comments separately.

Psuedocode:

• Get all the posts
  • Declare an array to store them in
  • Iterate the fetched posts, and for each of them:
    • Get the 'likes' and 'comments' separately from the posts (i.e. in their own queries). Get them as an array. Add them to the posts array like $posts['likes'] = $likes.
    • Convert the constructed array to json and echo it out: echo json_encode($posts)

Answer 2

SELECT * FROM `posts`
INNER JOIN `comments` ON `comments`.`postId` = `posts`.`postId`
INNER JOIN `likes` ON `likes`.`postId` = `posts`.`postId`
WHERE `posts`.`postId` = ?