Filter MYSQL results according to the result of a line

Question

I’m having a problem to perform a specific filter in a query in Mysql.

I am building a search system where I have the following tables:

• Questionnaires: with the name of the questionarios
• Questions: containing the questions in the questionnaire, with foreign key pointing to the questionnaire table
• Answers: containing the answers to each question, with the foreign key pointing to questions
• Tokens: Information on who replied
• Answers Given: Contains the answers given by each person.

I have the following structure:

[Tabela Fichas]
- ID
- created_at
- latitude
- longitude

[Tabela Respostas dadas]
- id
- ficha_id
- resposta_id
- pergunta_id

I need a consultation that returns the amount of respostas_id according to a specific question.

For example, I need to know who answered "23" in the field resposta_id when pergunta_id = "81", answered the other questions.

I’ve been hitting my head on this for days, but I’m not getting it. I even thought about creating a view that organized the data so that each question was a column and the answers were the lines, but also I could not.

Answer 1

[Tabela Respostas dadas]
- id
- ficha_id
- resposta_id
- pergunta_id

For example, I need to know who answered "23" in the field ansosta_id when question_id = "81", answered in the other questions.

answered "23" in the field answer_id when question_id = "81"

select id
from respostas
where pergunta_id = "81"
and resposta_id = "23" 

answered the other questions.

select *
from respostas
where pergunta_id <> "81"
and id in (select id
           from respostas
           where pergunta_id = "81"
           and resposta_id = "23")

I believe this is it. []s

Answer 2

Thank you to everyone who posted it. With Motta’s help I arrived at the following consultation:

 select respostas.valor, count(*) from respostas_dadas join respostas on
   respostas.id = respostas_dadas.resposta_id
   where respostas_dadas.pergunta_id <> "26"
    and ficha_id in
        (select ficha_id from respostas_dadas
        where pergunta_id = "26"
        and resposta_id = "82") group by respostas_dadas.resposta_id

This is a study system that I’m doing, thinking about a research company that has to assemble several questionnaires. In the table questionnaires i play the general data of each questionnaire, in the table questions I play the questions of this questionnaire on the table answers i play the answers to each question, on the table tokens i save the general data of each chip, when it was made, place, and finally the answers of each chip I play on the table answered. This filter I’m trying to do is for questions like

"Of people with male sex ( question 26, answer 23 ) how many will vote for mayor X"?

Thanks people. And sorry I can’t format the code, first time I’m posting here. I followed the orientation to give the 4 spaces but the query is not leaving formatted.