Need a complex sql query for a MySQL database

Question

I have the following database. One user can write one or more posts. Votes is many-to-many.

A user writes a post. Anyone can vote for the post, and thus the vote is saved in the vote table.

I need to display all the posts on a page. On each post, the post's author, post_votes, and the votes of the author need to be displayed.

My tables are:

Table: user

id | user_name
1  | John
2  | Sam
3  | Susan

Table: post

id | post_title | user_id
1  | Hello      | 1
2  | Sunday     | 1
3  | Monday     | 2
4  | Sun        | 1
5  | Sun        | 3


Table: votes

id | post_id

1  |  1
2  |  1
3  |  3
4  |  2
5  |  3
6  |  1

I need the query to return the following resultset:

post_id | post_votes | user_id | user_votes
1       | 3          | 1       | 4 
2       | 1          | 1       | 4
3       | 2          | 2       | 2  
4       | NULL       | 1       | 4    
5       | NULL       | 3       | NULL

I tried all sorts of combinations, but my brain just won't work. Also, would be very thankful if there is a better way to design the database so that retrieving the above can be made easy.

What exactly is that votes table for? As it stands now, it'd appear that a user can vote multiple times for any post. — Marc B
– Marc B, Commented Jul 9, 2012 at 18:09
@GordonLinoff Joining the post table and the vote table to get the total votes for a post, and then again joining the post table with the vote table to get the user's total votes, but I just got lost in the middle. Have been trying for a day, with no luck. — WebNovice
– WebNovice, Commented Jul 9, 2012 at 18:10
@MarcB yes, anyone can vote on a post and can vote multiple times according to the above table. In actual, I have an Ip address field in the votes table, that denys mutiple voting by same IP. But that is irrelevant here. Each record in the votes table means 1 vote for that particular post. — WebNovice
– WebNovice, Commented Jul 9, 2012 at 18:11

Gordon Linoff · Accepted Answer · 2012-07-09 18:46:34Z

1

This is a little complicated. In cases like this, you often want to calculate each aggregated column separately. In this case, use one subquery to get the number of posts and another to get the number of users.

select p.post_id, pv.post_votes, uv.user_id, uv.user_votes
from post p left outer join
     (select v.post_id, count(*) as post_votes
      from votes v
      group by v.post_id
     ) pv
     on p.id = pv.id left outer join
     (select p.user_id, count(*) as user_votes
      from votes v join
           post p
           on p.id = v.post_id
      group by p.user_id
     ) uv
     on p.user_id = uv.user_id

edited Jul 9, 2012 at 18:46

answered Jul 9, 2012 at 18:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

WebNovice Over a year ago

u.id as user_id but the user(u) table is no where mentioned.

Gordon Linoff Over a year ago

Oops. I wrote the "from" clause and forgot to finish the "select" part of it. I edited it now so it is correct.

WebNovice Over a year ago

Thank you very much. Think you made two small typos. on p.id = pv.id left outer join should be on p.id = pv.post_id and group by v.user_id should be group by p.user_id as votes(v) does not have user_id. Anyway, I changed those, and it works. Thank you very much. Things like this look very unsolvable for me, but looks very easy for you. Can you point me to somewhere, where I can improve a little more?

Gordon Linoff Over a year ago

It so happens I've written a book on this topic. You can check out amazon.com/Data-Analysis-Using-SQL-Excel/dp/0470099518/….

WebNovice Over a year ago

I definitely need to. Thanks alot once again.

MatBailie · Accepted Answer · 2012-07-09 18:13:54Z

0

SELECT
  post.id,
  post.user_id,
  post_votes.total,
  user_votes.total
FROM
  post
LEFT JOIN
  (SELECT post_id, COUNT(*) as total FROM votes GROUP BY post_id) AS post_votes
    ON post_votes.post_id = post.id
LEFT JOIN
  (SELECT user_id, COUNT(*) as total FROM votes GROUP BY user_id) AS user_votes
    ON user_votes.user_id = post.user_id

answered Jul 9, 2012 at 18:13

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

1 Comment

WebNovice Over a year ago

There is no user_id in the votes table, so the last line throws an error.

Collectives™ on Stack Overflow

Need a complex sql query for a MySQL database

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related