Good morning everyone, 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 id of the question and the id of the alternatives, what I want and count how many votes an alternative has received, and return the value with the title of the alternative, so far so good, the problem is that I can not return the questions with no vote, using an outer join has 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 the values including zeroes.
table response:
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;
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 want output: Image that has the issue bank branch, with the alternatives, bank of brazil, bradesco, itau, box and that only the bank of Brazil and the box has been voted, the return I want is:
Bank of Brazil - 1, Caixa - 1, Bradesco - 0, Itau - 0.