Filter MYSQL results according to the result of a row

2

I'm having a problem to perform a specific filter on a query in MySQL.

I'm building a search system where I have the following tables:

  • Questionnaires : with the name of the questionnaires
  • Questions : containing the questions of the questionnaire, with foreign key pointing to the questionnaire table
  • Answers : Containing the answers to each question, with foreign key pointing to questions
  • Bookmarks : Information about who responded
  • Given Answers : 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 query that returns me the amount of respostas_id according to a specific question.

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

I've been pounding for days on this but I can not. 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 I could not.

    
asked by anonymous 19.11.2015 / 15:15

2 answers

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

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

answered "23" in the response_id field 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 think this is it. [] s

    
19.11.2015 / 18:35
0

Thanks to everyone who posted. With the help of Motta I arrived at the following query:

 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 I'm doing, thinking of a research company that has to put together several questionnaires. In the questionnaires table I play the general data of each questionnaire, in the table questions I play the questions of this questionnaire in the table answers I play the answers of each question in the tokens table I save the general data of each token, when it was done, place, and lastly the responses of each token I play in the table given_responses . This filter I am trying to do is to type questions

  

"How many voters will vote for Mayor X (question 26, answer 23)?"

Thanks guys. And sorry I can not format the code, the first time I'm posting here. I followed the orientation of giving the 4 spaces but the excerpt of the query is not coming out formatted.

    
21.11.2015 / 00:41