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.
SELECT p.pid, p.pcontent, ifnull(l.like_cnt, 0) like_cnt, ifnull(c.cm_cnt, 0) comment_cnt FROM POST p LEFT OUTER JOIN (SELECT p.pid, COUNT(p.rid) like_cnt FROM plikestats p WHERE p.likestatus = 1 GROUP BY p.pid) l ON l.pid = p.pid LEFT OUTER JOIN (SELECT c.pid, COUNT(c.rid) cm_cnt FROM comment c group by c.pid ) c on c.pid=p.pid
Try this out,
This should work, or at least get you on the right path of getting the results you are looking for… pay attention to how the tables are alias and how the joins are used as that is key for getting the proper results…
1) SELECT P.PID , CONTACT(R.FNAME,' ',R.LNAME) AS FULL_NAME , P.PCONTENT FROM REGISTRATION R JOIN POST ON R.RID = P.RID 2) SELECT P.PID , P.PCONTENT , COUNT(PL.PID) AS TOTAL_LIKES FROM POST P JOIN PLIKESTATS PL ON P.PID = PL.PID WHERE PL.LIKESTATUS = 1 GROUP BY P.PID , P.PCONTENT 3) SELECT P.PID , P.PCONTENT , COUNT(C.PID) AS COMMENT_COUNT FROM POST P JOIN COMMENT C ON P.PID = C.PID GROUP BY P.PID , P.PCONTENT
WITH post_like as ( SELECT pid, COUNT(rid) as like_count FROM plikestats WHERE likestatus = 0 GROUP BY pid ), post_comment as ( SELECT pid, COUNT(rid) as comment_count FROM comment GROUP BY pid ), user_post as ( SELECT post.pid AS PID , CONCAT(registration.fname, ' ', registration.lname) AS NAME , post.pcontent AS CONTENT FROM registration JOIN post WHERE post.rid = registration.rid ) SELECT user_post.name, user_post.CONTENT, post_like.like_count, post_comment.comment_count FROM user_post JOIN post_like ON user_post.PID = post_like.pid JOIN post_comment ON user_post.PID = post_comment.pid;