group_contact using left Join mysql and separator

Asked

Viewed 49 times

0

Good afternoon, folks. I’m having a question how to mount a query using group_concat.

I have 2 tables:

Prova
ID     Name        Respostas_ID      Status
1      Teste 1     1,2               1
2      Teste 2     2,4,5             1
3      Teste 3     4,5               0

On the second table I have:

Respostas
ID      Name
1       Resposta A
2       Resposta B
3       Resposta C
4       Resposta D
5       Resposta E

Through the Table A I link the existing ads on Table B

I am mounting following query:

SELECT
    prova.id,
    GROUP_CONCAT( DISTINCT `respostas`.`name` SEPARATOR ',' )
FROM
    LEFT JOIN respostas ON prova.respostas_id = respostas.id
WHERE
    prova.`status` = 1 
GROUP BY
    prova.id

When making this query it only brings me the first value of the table:

ID      Name        Respostas
1       Teste 1     Resposta A
2       Teste 2     Resposta B

But I would need him to return to me as follows:

ID      Name        Respostas
1       Teste 1     Resposta A,Resposta B
2       Teste 2     Resposta B,Resposta D,Resposta E

For this result I am mounting in a MYSQL view to not need to bring results that are disabled. The idea of this is to have a better optimization of the bank.

If anyone can help me or has a better suggestion of how to do I’m grateful.

Hugs

mysql query

  • Your Respostas_id field is multi-valued?

  • What is multivariate? If what I imagine is yes, it receives the answers to each question and stores it in the proof table. @anonimo

  • In this case it is not possible to use JOIN because the fields specified in the ON clause will be different. Maybe it’s possible if you do a great workout by searching for field substrings.

  • I get it, I’m going to try to think of a better way to do this

  • I was able to solve this using a Mysql function called FIND_IN_SET. I’ll put it like I did. @anonimo

1 answer

0


I solved the issue using the Mysql FIND_IN_SET function

The result of the consultation was thus:

SELECT
    prova.id,
    GROUP_CONCAT( DISTINCT `respostas`.`name` SEPARATOR ',' )
FROM
    LEFT JOIN respostas ON FIND_IN_SET(respostas.id, prova.respostas_id)
WHERE
    prova.`status` = 1 
GROUP BY
    prova.id

Browser other questions tagged mysql query

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