Ilias Ahmed Ilias Ahmed - 11 days ago 8
MySQL Question

mysql - get all post with comment count and user from users, posts and comments table

I have 3 tables of users, posts and comments. Users table fields are

Id, name, gender, phone

posts table fields are

post_id, user_id, posts, time

comments table fields are

id, user_id, comment, post_id, time

i wants to get value of these fields

name, gender, posts, time, count(comments)

Answer
SELECT x.*, CONCAT(u.FirstName, ' ', u.LastName) as name, u.Gender as gender 
FROM (
    SELECT
    a.id , a.post, a.date_time, a.user_id as uid,
    COUNT(c.post_id) AS 'count'
FROM
    posts a
LEFT JOIN
    comments c ON a.id = c.post_id
GROUP BY
    a.id
) x 
LEFT JOIN users u ON u.id=uid
Comments