How to show repeated data only once?

Navigation
3

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 its authors, however there are some books that have more than 1 author ie 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 prevent the name of the book from being repeated and the names of the authors of the same book to join together?

    
asked by anonymous 17.10.2017 / 00:52

2 answers

7

I believe you are looking for 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

You can also group by titulo_livro if your model ensures that there are no books with the same title (ie titulo_livro is a single key in tb_livro ), but the solution with the identifier is more robust

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

    
17.10.2017 / 01:21
0

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;
    
17.10.2017 / 00:57
Customize boxplot How to open an activity from a java notification in android?