Mysql – Query to join three tables

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

You are on the right track. Don't let perceived complexity derail the design, as it is basically the standard way to represent many-to-many relationships. These tables are also a good place to store metadata or attributes of the entity relationship.

Your reasoning is mostly correct: there are data integrity benefits to using a DBMS-generated key in the intermediate table approach, but an integer isn't necessarily faster on key comparisons because ideally the joins would use indexes, not the column values.