Record values of a related table concatenated in the main entity query

Asked

Viewed 26 times

2

I have two tables: ordem_servico and ordem_servico_tecnicos. If more than one technician is required for an O.S., records of each technician are created in the second related table by the order number(id).

I would like to consult open orders showing the technicians related to it.

    SELECT
     os.numero_os AS numero_os
    FROM
    ordem_servico os
    WHERE
    os.data_servico BETWEEN '$data_cad_i' AND '$data_cad_f'

With subquery gave the error: "Subquery Returns more than 1 Row" because it actually has more than one technique for the same O.S..

mysql

  • It’s not just making one INNER JOIN? https://answall.com/questions/6441/qual%C3%A9-a-difference%C3%A7a-entre-Inner-Join-e-outer-Join

  • No. Imagine that where 1234 has technicians A and B, then in the table of ordem_servico_tecnicos There will be two records, one for each technician. What I would like is to have a return line of the service order consultation with the name of the two technicians who will execute it.

  • Rafael, do you say in the same line? Without repeating the order?

  • This, the technicians appear on the same line of OS data in one field only, as if they were concatenated.

  • Try to understand about functions, they can solve your problem.

  • I corrected my answer in an attempt to help you. Look there.

Show 1 more comment

1 answer

1


Using the function group_concact you can concatenate in the same column all the records of your table of technicians by id and nome.

Note that the sub-query in the daughter table with this function.

select os.numero_os as numero_os,
    (select group_concat(`nome` separator ',') as `tecnicos`
     from  (select id_servico,
                   concat(`nome`, ':', group_concat(`Value` separator ',')) as `nome`
            from ordem_servico_tecnicos
            group by id_servico, `nome`) tbl
     where tbl.id_servico = os.id_servico) as tecnicos
from ordem_servico os
where os.data_servico between '$data_cad_i' and '$data_cad_f'

  • Thank you Ismael. But with this, if I have 3 technicians in the same O.S., the query will return me 3 lines, what I would like is to have a single line and a field with the name of the 3 technicians who were indicated for the service.

  • Your question had not been clear enough to understand that it was not just a Join Inner. I will rework the answer so as not to have to remove it.

  • Thanks... the group_concact function solved.

Browser other questions tagged mysql

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