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.
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