Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have three tables:

  1. posts - contains the posts by users,
  2. post_reviews - contains the likes on the posts, and
  3. 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
}
share|improve this question
    
Please supply more information. Run these SHOW CREATE TABLE posts\G, SHOW CREATE TABLE posts_reviews\G and SHOW CREATE TABLE posts_share\G and post their output in the question. –  RolandoMySQLDBA Oct 16 '12 at 14:46
    
@RolandoMySQLDBA - Updated question with the table structures. –  steady_daddy Oct 16 '12 at 14:56

1 Answer 1

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

share|improve this answer
    
@RonaldoMySQLDBA - Thank you for the effort. I will start with this. –  steady_daddy Oct 16 '12 at 15:47
    
There was a syntax error I saw. I corrected it. –  RolandoMySQLDBA Oct 16 '12 at 15:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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