N-to-N relationship - How to join two fields in a Postgresql record -

Question

N-to-N relationship - How to join two fields in a Postgresql record -

Navigation

#1 by (0 votes)

2

In an N-to-N relationship I have three tables "book", "author" and "book_autor". Being the following data and structures:

tblBook:

id_livro | nomelivro
'1'      ; 'O Cortiço'
'2'      ; 'O mulato'
'3'      ; 'Quimica Geral'

tblAutor

id_autor | nomeautor
'1'      ; 'Aluísio Azevedo'
'2'      ; 'John C. Kotz'
'3'      ; 'Paul M. Treichel'
'4'      ; 'Gabriela C. Weaver'

tbl_Author

id_livro_autor | id_livro | id_autor. 
'1'            ; '1'      ; '1'
'2'            ; '2'      ; '1' 
'3'            ; '3'      ; '2' 
'4'            ; '3'      ; '3' 
'5'            ; '3'      ; '4'

I would like to do a search that would return the following result:

Livro           | Autor
'O Cortiço'     ; 'Aluísio Azevedo'
'O mulato'      ; 'Aluísio Azevedo'
'Quimica Geral' ; 'John C. Kotz, Paul M. Treichel, Gabriela C. Weaver'

When I use the following SQL command:

Select tblLivro.nomelivro, tblAutor.nomeautor, 
from tblLivro
left outer join tblLivro_Autor on tblLivro.id_livro=tblLivro_Autor.id_livro
left outer join tblAutor on tblLivro_Autor.id_autor=tblAutor.id_autor

The book General Chemistry appears three times (one recorder for each author).

modelo-relacional postgresql

asked by anonymous 30.03.2016 / 16:21

1 answer

Improve the code to just get the value of the localstorage [closed] Select from a table inside a cell of another table [closed]

score 0 · Accepted Answer

To add the value of multiple rows in a column you can use the string_agg () which is equivalent to the MySQL group_concat

SELECT livro.nome_livro, string_agg(autor.nome_autor,',') FROM livro as l
LEFT OUTER JOIN livro_autor as la ON l.id_livro = la.id_livro
LEFT OUTER JOIN autor as a ON la.id_autor = a.id_autor
GROUP BY l.nome_livro

Example - sqlfiddle