Get all matched row using SQL query

Question

I have the posts table with the following data /structure:

posts_id   post_message   u_id  like_count  created
===================================================
1         content.......  1     25          1559041633
2         content.......  2     25          1559041633

and posts_like table with the following data/structure:

like_id    posts_id   u_id   r_id    created
=============================================
22         2          1      2       1559041633
27         2          2      4       1559041633
30         1          2      7       1559041633

Note: u_id means the user id, r_id means reaction id, posts_id means posts id.

Now, I want to get all posts from the posts table along with the full name from the users table and all u_id and r_id under each post.

For example, you can see that under posts_id = 2 I have u_id 1 and 2 and r_id 2 and 4.

Desire Output:

first post content  
user full Name
u_id: 2
r_id: 7

second post content  
user full Name
u_id: 1, 2
r_id: 2, 4

Current I am using this SQL query

$query = $this->_db->_pdo->prepare("
SELECT p.posts_id, p.post_message, p.like_count, p.created, u.full_name,
 u.u_id, pl.r_id FROM posts AS p LEFT JOIN users AS u ON u.u_id = p.u_id 
LEFT JOIN posts_like AS pl ON pl.posts_id = p.posts_id GROUP BY 
p.posts_id ORDER BY p.posts_id DESC ");

This above query does not get the all u_id and r_id under each post.

Nick · Accepted Answer · 2019-05-30 06:03:43Z

1

You need to use a GROUP_CONCAT on your u_id and r_id fields to get a list of all the reactions. Note that you need to use pl.u_id, not u.u_id as u.u_id refers to the post creator, not the person who has reacted to the post.

SELECT p.posts_id, p.post_message, p.like_count, p.created, 
       u.full_name,
       GROUP_CONCAT(pl.u_id ORDER BY pl.u_id) AS u_id,
       GROUP_CONCAT(pl.r_id ORDER BY pl.u_id) AS r_id
FROM posts AS p 
LEFT JOIN users AS u ON u.u_id = p.u_id 
LEFT JOIN posts_like AS pl ON pl.posts_id = p.posts_id 
GROUP BY p.posts_id
ORDER BY p.posts_id DESC

Output:

posts_id    post_message    like_count  created     full_name   u_id    r_id
2           content.......  25          1559041633  Mary Brown  1,2     2,4
1           content.......  25          1559041633  John Smith  2       7

Demo on dbfiddle

edited May 30, 2019 at 6:03

answered May 30, 2019 at 5:47

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Shibbir Over a year ago

Then It's showing me 3 posts but I have 2 posts in the posts table.

Nick Over a year ago

@creativeartbd Sorry, I misinterpreted your question. See my edit.

Shibbir Over a year ago

one more help needed if you can. I want to get the full name corresponding to u_id. can I get?

Shibbir Over a year ago

I get the u_id list and I can use another query to get the full name of this u_id's. but can I get it using the main query?

Nick Over a year ago

@creativeartbd you just need to join to the users table again to get those values. See this fiddle: db-fiddle.com/f/uWCJ497p1dqK77MaNswJQ9/1

|

Collectives™ on Stack Overflow

Get all matched row using SQL query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related