View multiple records within one in Mysql

Question

I have a question as to the mounting of a select.

I have 3 tables that relate M:N as follows:

Currently I am using this sql:

select p.descricao,
       (select descricao from item where item.codigo = c.codigoItem) as items  
from produto p inner join composicao c on c.codigoProduto = p.codigo 
inner join item i on i.codigo = c.codigoItem 
order by p.codigo

and the result comes like this:

Beauty, however it would be possible to make an output as in the image below direct by sql?

Has some function to define a group by or you’d have to ride a stored Procedure? At first I’m solving the issue of direct display in Java, testing if at each iteration the product is the same.

Answer 1

You can use the function GROUP_CONCAT(expr) mysql.

Your SQL query would look like this:

select p.descricao,
  GROUP_CONCAT( (select descricao from item where item.codigo = c.codigoItem) SEPARATOR ',' )
from produto p inner join composicao c on c.codigoProduto = p.codigo 
inner join item i on i.codigo = c.codigoItem 
group by p.descricao
order by p.codigo

You can see more about the function and its details in the documentation:

https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-Concat

Answer 2

You can do it this way, using GROUP_CONCAT:

select p.descricao, 
       (select
       GROUP_CONCAT(i.descricao  separator ', ')
       from item where item.codigo = c.codigoItem)
       as items  
from produto p inner join composicao c on c.codigoProduto = p.codigo 
inner join item i on i.codigo = c.codigoItem 
group by p.descricao
order by p.codigo

The function GROUP_CONCAT will group the conditional results in the same row using a specified separator, in the case ", " (comma followed by space).

Result (tested in Mysql-Front):

Obs.: not to sound like a copy, I was writing the answer when the other one was published. As they both looked alike but with a construction and a slightly different result, I decided to keep this response active as an option.