Selecting the popular posts using three tables I have three tables:


posts - contains the posts by users,
post_reviews - contains the likes on the posts, and
post_share - contains the post id and user id of that which is shared.


I would like to select five popular posts using these three tables. The popular posts are those that have the highest no of reviews and shares. I am finding it really hard to construct a SQL query for this.

A slight help or guidance in this matter will be highly appreciated.

UPDATE:

The table structure is as follows:


table posts{

id - PK
uid - FK
post - text
}

table post_reviews{

id - PK
pid - FK (Post Id)
uid - FK
like_unlike - ENUM
}

table post_share{

id - PK
pid - FK (Post Id)
uid - FK
}
5 Most Popular Posts By Reviews

SELECT A.uid,B.post_count reviews,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_reviews
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;


5 Most Popular Posts By Shares

SELECT A.uid,B.post_count shares,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_shares
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;


I hope this gives you a start...