Select with data from associative tables

Asked

Viewed 436 times

0

I am creating a small database to store the books I own at home, but I came across the following situation: I have two associative tables, a call self-published, that allows the same book to have several authors and a call genero_book, which allows a particular book to be part of one or more genres (two, to be exact). My problem is time to create a SELECT that brings the information from the books (for example: authors, page Qtd), but also brings what genres this book is part of. I managed to create the query, but my code is repeating several times the authors and the generos of the books. What would be causing this repetition?

Follow my code (I am using MYSQL):

SELECT group_concat(a.nome) as 'Autores', l.titulo as 'Título', l.pag as 'Páginas', group_concat(g.tipo), e.nome as 'Editora' 
FROM autor_livro al
INNER JOIN autor a ON a.id_autor = al.autor
INNER JOIN livro l ON l.id_livro = al.livro
INNER JOIN editora e ON e.id_editora = l.editora
INNER JOIN genero_livro gl ON gl.livro = al.livro
INNER JOIN genero g ON g.id_genero = gl.genero
GROUP BY al.livro;
  • I see you didn’t comment on the final output. Do you only want to be able to see the data in phpMyadmin (or another mysql program...)? Is it personal query only? If you are using php to concatenate the resultadps you can create associative arrays to group them together. If this is the case I can give examples, but if it is only by MYSQL I think it could not help mto...

  • Sorry, buddy. For now I haven’t implemented for any language yet. I’m only using MYSQL myself.

  • https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-Concat Leai sobre group_concat may be a solution in this case

  • @Motta Amigo, that’s what was missing. When I added "distinct" to my group_concat(), it stopped displaying repeated data. Would it be correct to use distinct whenever you use group_concat? I appreciate the help!

  • The group_contact dismisses the distinct , its output will have something like :: the return of q ñ foram manuel da silva/Joaquim da silva

No answers

Browser other questions tagged

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