MYSQL QUERY triple join triple count-CodePudding

I need to perform a query but can't make it works... Any help would be appreciated !
Performed on MYSQL WORKBENCH
I got a DB made of 4 tables :

user	Posts	likes	comments
username	id	id	id
id	title	userId (foreign key from user.id)	content
email	content	postId (foreign key from posts.id)	date
password	date		userId (foreign key from user.id)
image	imageUrl		postId (foreign key from posts.id)
bio	userId (foreign key from user.id)
createDate

I want a query that would take specifics datas from USER plus a total count of likes, posts and comments for a specific user and a total result of 0 if no likes / posts / comments exist within this user. I basically need stats for my user.

My query at the moment give me the good infos for username / permission / image / bio and createDate but return me 0 for all counts.

SELECT u.username, u.permission, u.image, u.bio, u.createDate,
COALESCE(P.totalPosts, 0) AS totalPosts,
COALESCE(C.totalComms, 0) AS totalComms,
COALESCE(L.totalLikes, 0) AS totalLikes
FROM user AS u 
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalPosts
    FROM posts
) AS P ON P.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalComms
    FROM comments
) AS C ON C.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalLikes
    FROM likes
) AS L ON L.userId = u.id
WHERE u.username = 'Alfred';

Thanks for your time <3 !

CodePudding user response：

What you are missing is:

GROUP BY userId

in all your subqueries:

SELECT u.username, u.permission, u.image, u.bio, u.createDate,
       COALESCE(P.totalPosts, 0) AS totalPosts,
       COALESCE(C.totalComms, 0) AS totalComms,
       COALESCE(L.totalLikes, 0) AS totalLikes
FROM user AS u 
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalPosts
    FROM posts
    GROUP BY userId
) AS P ON P.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalComms
    FROM comments
    GROUP BY userId
) AS C ON C.userId = u.id
LEFT JOIN(
    SELECT userId, COUNT(*) AS totalLikes
    FROM likes
    GROUP BY userId
) AS L ON L.userId = u.id
WHERE u.username = 'Alfred';

But, all these subqueries do aggregation for all users and not only for the specific user that you want the results and this is bad for performance.

It would be better if you used correlated subqueries:

SELECT u.username, u.permission, u.image, u.bio, u.createDate,
       (SELECT COUNT(*) FROM posts p WHERE p.userId = u.id) AS totalPosts,
       (SELECT COUNT(*) FROM comments c WHERE c.userId = u.id) AS totalComms,
       (SELECT COUNT(*) FROM likes l WHERE l.userId = u.id) AS totalLikes
FROM user AS u
WHERE u.username = 'Alfred';