2

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.

0
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
  • It will run a lot faster if you move those two subqeries into the SELECT part. – Rick James Aug 4 '16 at 22:05
  • @RickJames It may execute the query faster, but it won't solve my purpose, as I have two left join tweaked query. – Abhishek Raval Aug 5 '16 at 4:06
0

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
  • I know how to use aliases, but I am getting trouble at solving the query as a whole. What you have done is reformatted all my queries into aliases, but it doesn't solve the purpose, I am unable to count comments as well as number of likes simultaneously. – Abhishek Raval Aug 4 '16 at 16:30
  • SELECT P.PID , P.PCONTENT , COUNT(PL.PID) AS TOTAL_LIKES , COUNT(C.PID) AS COMMENT_COUNT FROM POST P JOIN PLIKESTATS PL ON P.PID = PL.PID AND PL.LIKESTATUS = 1 LEFT JOIN COMMENT C ON P.PID = C.PID GROUP BY P.PID , P.PCONTENT I used this query as combination and had have used it before too, but I am getting same counter values for Totalcomments as well as total likes. – Abhishek Raval Aug 4 '16 at 16:44
0
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;
  • 1
    This would be better with an explanation of the techniques used, how it differs from the other answers, and under what circumstances it will be better than them. – Michael Green Jun 6 '18 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.