List zeroed results in query

Asked

Viewed 50 times

0

Good morning, you guys, I need your help in a select, I’m doing a search, I have a 4 tables: user, question, alternatives and answer. In the answer table I save the user’s Cpf, the question id and the alternatives id, what I want and count how many votes determined alternative received, and return the value with the alternative title, so far so good, the problem and that I can not return the questions with no vote, using an Outer Join worked partially, if I show all, returns the zeroes, however, if I filter for a specific question it returns only the questions with votes, how do I return all values including zeroes.

response table:

create table tb_resposta (
cpf_usuario varchar(14) not null,
id_pergunta int not null,
id_resposta int not null,
foreign key (cpf_usuario) references tb_usuario(cpf),
foreign key (id_pergunta) references tb_pergunta(id),
foreign key (id_resposta) references tb_alternativa(id)
);

SELECT:

select a.titulo, count(r.id_resposta), r.id_pergunta from tb_resposta as r
right join tb_alternativa as a on a.id = r.id_resposta
where r.id_pergunta = 2
group by a.titulo;

in select above, if I take Where it shows all including zeroes, but with Where, it does not show.

the other tables:

create table tb_pergunta (
    id int auto_increment not null primary key,
    titulo varchar (100)
);

create table tb_alternativa (
    id int auto_increment not null primary key,
    titulo varchar (100),
    id_pergunta int not null,
    foreign key (id_pergunta) references tb_pergunta(id)
);

create table tb_usuario (
    cpf varchar(14) not null primary key,
    idade int not null,
    sexo enum ('M', 'F') not null
);

More detailed example of how I wish to exit: Image that has the bank branch issue, with the alternatives, bank of Brazil, Radesco, Tau, cash and that only the bank of Brazil and the box have been voted, the return I want is:

Banco do Brasil - 1, Caixa - 1, Bradesco - 0, Itau - 0.

mysql sql

  • How are the answers saved without a marked alternative? It would be interesting if you put an example of database data to make it clear how your data is persisted in each case.

  • Exchange RIGHT JOIN for INNER JOIN.

  • @Giulianabezerra in the responsible table is saved CPF - ID_PERGUNTA - ID_RESPOSTA.

  • @Eliseub. It doesn’t work, I’ve tried it too.

  • I did not understand what the expected result. Is to return only how many times an alternative has been marked your title? Why is there a return of the question id that is outside the group by?

  • @Paulor.F.Amorim for example, there is the alternative banks for example, there will be who knows, bank of Brazil, cashier, Tau and Radesco, let’s assume that only bank of Brazil and Tau was voted, then I want to return, bank of Brazil - 1, Tau - 1, Radesco - 0, cashier - 0, I already tried to add the fields in group by and still does not return as I want.

  • see if it helps https://forum.imasters.com.br/topic/462564-selectr-todos-dias-do-m%C3%Aas-e-quantity-of-sales/

Show 2 more comments

1 answer

0


I managed to solve the problem by adding a few more conditions in the Where clause:

select a.titulo, count(r.id_resposta), r.id_pergunta from tb_resposta 
as r
right join tb_alternativa as a on a.id = r.id_resposta
where (r.id_pergunta = 2 and r.id_pergunta is not null) or 
(r.id_pergunta is null and a.id_pergunta = 2)
group by a.titulo;

It checks the answer table, by id, if the field is null it looks for the id in the alternative table, which has the question id, so I can list all the alternatives, including with zeroes.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.