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

2

I have two tables: server_and_server_servername. If you require more than one technician for an O.S., records of each technician are created in the second table listed by the order number (id).

I would like to make an open order query that would show 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 ..

    
asked by anonymous 28.04.2017 / 16:37

1 answer

1

Using the group_concact you can concatenate in the same column all records in your technician table by grouping them by id and nome .

Note that the subquery in the child 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'
    
28.04.2017 / 16:51