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.