stack stack - 3 months ago 7
MySQL Question

How can I select posts that have more than specific votes?

My tables structure:

// posts
+----+----------------------------------------+-----------+
| Id | body | user_id |
+----+----------------------------------------+-----------+
| 1 | content1 | 2 |
| 2 | content2 | 9 |
| 3 | content3 | 6 |
| 4 | content4 | 4 |
| 5 | content5 | 2 |
| 6 | content6 | 8 |
| 7 | content7 | 4 |
| 8 | content8 | 2 |
+----+----------------------------------------+-----------+

// votes
+----+---------+-------+
| id | post_id | value |
+----+---------+-------+
| 1 | 2 | 1 |
| 2 | 3 | -1 |
| 3 | 2 | 1 |
| 4 | 8 | -1 |
| 5 | 1 | 1 |
| 6 | 8 | 1 |
| 7 | 2 | -1 |
| 8 | 8 | -1 |
| 9 | 2 | 1 |
+----+---------+-------+


I need to select posts that have more than
1
vote total score. So this is expected output:

+----+----------------------------------------+-----------+-------------+
| Id | body | user_id | total_votes |
+----+----------------------------------------+-----------+-------------+
| 2 | content2 | 9 | 2 |
+----+----------------------------------------+-----------+-------------+


How can I do that?

Answer
create table qanda
(   id int not null,
    body varchar(100) not null,
    user_id int not null
);
insert qanda values
(1,'a',2),
(2,'a',9),
(3,'a',6),
(4,'a',4),
(5,'a',2),
(6,'a',8),
(7,'a',2),
(8,'a',2);

create table votes
(   id int not null,
    post_id int not null,
    value int not null
);
insert votes values
(1,2,1),
(2,3,-1),
(3,2,1),
(4,8,-1),
(5,1,1),
(6,8,1),
(7,2,-1),
(8,8,-1),
(9,2,1);

Query

 select q.id,q.body,q.user_id,sum(v.value) as votes 
 from qanda q 
 join votes v 
 on v.post_id=q.id 
 group by q.id,q.body,q.user_id
 having votes>1;


+----+------+---------+-------+
| id | body | user_id | votes |
+----+------+---------+-------+
|  2 | a    |       9 |     2 |
+----+------+---------+-------+