How to show the data repeated only once?

Question

Well, I have the following problem in Mysql: I created two tables a table of books and another table of authors. Well, I need to show the name of the book with their respective authors, but have some books that have more than 1 author or using the following SQL:

select l.titulo_livro, a.nome_autor, a.id_autor from tb_livros as l 
join tb_autores as a
where l.id_livro = a.id_livro_escrito;

I get the following result:

Is there any way to ensure that the name of the book is not repeated and that the names of the authors of the same book are joined?

Answer 1

I believe you’re looking for the job GROUP_CONCAT:

select l.id_livro, 
    MIN(l.titulo_livro), 
    GROUP_CONCAT(a.nome_autor),
    GROUP_CONCAT(a.id_autor)
from tb_livros as l join tb_autores as a
    on (l.id_livro = a.id_livro_escrito)
group by l.id_livro

It is also possible to group by titulo_livro if your model ensures that there are no books with the same title (i.e., titulo_livro is a unique key in tb_livro), but the solution with the identifier is more robust.

The function MIN is not strictly required in Mysql since titulo_livro is functionally dependent of id_livro. That said, for compatibility reasons with other banks I chose not to use directly a column that was not listed in the clause group by. Newer versions of Mysql even expose a function ANY_VALUE to deal with this kind of situation.

Answer 2

With a distinct it will eliminate the same results:

   select distinct l.titulo_livro, a.nome_autor, a.id_autor from tb_livros as l 
join tb_autores as a
where l.id_livro = a.id_livro_escrito;