1

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).

Improve this question
1
  • You can find all the needed info on joins here Commented Jun 8, 2011 at 22:40

2 Answers 2

Reset to default
4

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

I am then outputting the results as JSON though.
If you're using JSON, create a wrapper object that contains the post information, likes and comments as separate properties of the wrapper object.
Also, one idea if you want to avoid multiple db queries, loop through the first list of posts, build up an array of Post ID's with the data you already have, then perform one query to get all the likes for the list of post ID's then farm the results off to their appropriate post id back in the original array. do the same for comments.
Just a question about this. Is this the 'right way' to approach a situation like this, or does it go against the concept of the relational databases?
There is no way I can read all that code inside a comment. If this issue is sufficiently different to the current one, please consider opening a new question about it?
|
2 
SELECT * FROM `posts`
INNER JOIN `comments` ON `comments`.`postId` = `posts`.`postId`
INNER JOIN `likes` ON `likes`.`postId` = `posts`.`postId`
WHERE `posts`.`postId` = ?
Improve this answer

7 Comments

This is not true. The joins in this query are constrained by the conditions in the ON statements within the joins.
yes, but if you have a post with 5 comments and 10 likes, you will get 50 rows for that post.
True, but this is what was requested by the question. My answer answers the original question, but Michael Robinson's answer is a better overall solution.
@KOGI I believe it is cartesian, isn't it? Every resulting row will contain the post, some of the lines will contain a comment, and some a like.
True, this does answer the question as asked.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.