Julien Julien - 5 years ago 97
SQL Question

SQL : select top 4 results with the higher votes UP and lower votes DOWN

I have like to do sthg simple, but I have no idea where to start.

so I have 4 tables :



  • Table question (id_question)

  • Table trad (id_trad, #id_question)

  • Table vote_up (id_vote_up, #id_trad)

  • Table vote_down (id_vote_down, #id_trad)




for the id_question=1, I would like to select the 4 translations (from trad) with the higher number of vote_up and with the lower number of vote_down

is it possible to do that with a single query? Any idea?

or maybe it's better to simply add "upvotes" and "downvotes" and update the corresponding field by 1 ?

Answer Source

plan

  • get count of upvotes grouped by each trad
  • get count of downvotes grouped by each trad
  • left join all trad to above datasources with score function, order by and limit of 4

example input

create table question
(
  id_question integer primary key not null
  -- other metadata here..
);

create table trad
(
  id_trad integer primary key not null,
  id_question integer not null,
  foreign key ( id_question ) references question ( id_question )
);

create table vote_up
(
  id_vote_up integer primary key not null,
  id_trad integer not null,
  foreign key ( id_trad ) references trad ( id_trad )
);

create table vote_down
(
  id_vote_down integer primary key not null,
  id_trad integer not null,
  foreign key ( id_trad ) references trad ( id_trad )
);

insert into question
( id_question )
values
( 1 )
;

insert into trad
( id_trad, id_question )
values
( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 1 ),
( 5, 1 ),
( 6, 1 ),
( 7, 1 )
;

insert into vote_up
( id_vote_up, id_trad )
values
( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 1 ),
( 5, 1 ),
( 6, 1 ),
( 7, 3 ),
( 8, 3 ),
( 9, 3 ),
( 10, 3 ),
( 11, 4 ),
( 12, 4 ),
( 13, 5 ),
( 14, 6 ),
( 15, 6 ),
( 16, 7 ),
( 17, 7 ),
( 18, 7 )
;

insert into vote_down
( id_vote_down, id_trad )
values
( 1, 1 ),
( 2, 1 ),
( 3, 1 ),
( 4, 1 ),
( 5, 1 ),
( 6, 1 ),
( 7, 3 ),
( 8, 3 ),
( 9, 3 ),
( 10, 4 ),
( 11, 4 )
;

query

select trad.id_trad, coalesce(upvotes, 0) - coalesce(downvotes, 0) as score
from trad
left join
(
select 
trad.id_trad, count(*) as upvotes
from trad
inner join vote_up
on trad.id_trad = vote_up.id_trad
where trad.id_question = 1
group by 1
) uv
on trad.id_trad = uv.id_trad
left join
(
select 
trad.id_trad, count(*) as downvotes
from trad
inner join vote_down
on trad.id_trad = vote_down.id_trad
where trad.id_question = 1
group by 1
) dv
on uv.id_trad = dv.id_trad
order by score desc
limit 4
;

output

+---------+-------+
| id_trad | score |
+---------+-------+
|       7 |     3 |
|       6 |     2 |
|       3 |     1 |
|       5 |     1 |
+---------+-------+

sqlfiddle ( separate structures )


note

consider also restructuring your votes into one table. atm vote_up and vote_down are unnecessarily duplicating the same structure.. this would look like :

sqlfiddle ( reuse structure )

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download