I am unable to join tables in correct manner

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;