I have created Four Tables-
1) Registration Table – it has registration id(rid),First Name of user(fname), Last Name of user(lname), gender(gender),birth date(dob).
2) Post Table – it has post id(pid), post content(pcontent), user who published post(rid as foreign key from registration table), time.
3) Plikestats Table – (where it has status of which post is liked by which user)- plikeid(unique id), post id which is liked or not(pid), user's id who have either liked the post or not(rid as foreign key), likestatus(0 for liked, 1 for unliked).
4) comment Table – (where details of all the comments will be stored.)- it has comment id(cid), post id on which comment is posted(pid as foreign key), user's id who is commenting(rid as foreign key), comment content(ccontent), time.
I have the following query: View all of a users’ posts and display the number of likes and number of comments to that post.
I am trying to get this query by using two count functions and two group bys. I have created three separate queries, which I am listing, but I am not getting complete logic for the query.
1) SELECT post.pid
, CONCAT(registration.fname, ' ', registration.lname) AS NAME
, post.pcontent
FROM registration JOIN post
WHERE post.rid = registration.rid
//user’s all post
2) SELECT post.pid
, post.pcontent
, COUNT(plikestats.pid) AS totallikes
FROM post, plikestats
WHERE post.pid = plikestats.pid
AND plikestats.likestatus=1
GROUP BY plikestats.pid
//number of likes to that post
3) SELECT post.pid
, post.pcontent
, COUNT(comment.pid) AS commentcount
FROM post, comment
WHERE post.pid = comment.pid
GROUP BY comment.pid
//number of comments to that post.
So far I have tried using Joins in many way, variables, but nothing works out. If anyone can help with it please.
Best Answer