SQL: Sort List by Vote Numbers from Another Table

Question

I want the results of my poll to be sorted by the amount of votes you have in the other exclusive table to compute votes.

Currently my results come out like this:

I have two tables, this one below belongs to the answers to be voted. The column enquete_id indicates the number of the poll that is linked to the voting responses.

CREATE TABLE `respostas` (
  `id` int(11) NOT NULL,
  `enquete_id` int(11) NOT NULL,
  `opcao` varchar(500) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

And this table belongs to the votes, this is where they will be computed:

CREATE TABLE `votos` (
  `id` int(11) NOT NULL,
  `enquete_id` int(11) NOT NULL,
  `opcao_id` int(11) NOT NULL,
  `data` int(11) NOT NULL,
  `ip` varchar(100) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I tried something like that, but it didn’t work out, even the times it did, Unfortunately there were no zeroed votes, no options to vote that were without votes.

SELECT * 
FROM resposta op 
INNER JOIN votos vt ON (vt.enquete_id = op.enquete_id) 
WHERE op.enquete_id='1' 
GROUP BY op.id ORDER BY vt.opcao_id

Reference

Answer 1

"Unfortunately there were no zeroed votes, no options to vote that were without votes". By that description and by your example, the problem is in join.

When using INNER JOIN, causes, a related record exists the same values in the two tables, i.e., the "enquete_id" must exist in both tables.

To bring the values "zero" IE, without votes, you need to make a LEFT Join (if you have questions, see this question: what is the difference between Ner Join and Uter Join), but this way, if the record does not exist in the "votes" table, it will return NULL, then you can use the Function ifnull to return zero instead of null, so the query looks like this:

SELECT COUNT(IFNULL(vt.enquete_id, 0)) votos
  FROM resposta op 
  LEFT JOIN votos vt ON (vt.enquete_id = op.enquete_id) 
 WHERE op.enquete_id='1' 
 GROUP BY op.id 
 ORDER BY vt.opcao_id, votos

EDIT: for example, with the specific data here: http://sqlfiddle.com/#! 9/309804/11 it was possible to notice that Join also needs to be done with the option voted, in which case the query was like this:

SELECT op.*, COUNT(vt.opcao_id) votos
  FROM resposta op 
  LEFT JOIN votos vt ON vt.enquete_id = op.enquete_id
        AND vt.opcao_id = op.id
 WHERE op.enquete_id = 1 
 GROUP BY vt.enquete_id, vt.opcao_id
 ORDER BY votos DESC, op.opcao;