Reducing quantities of rows in a query

Question

Reducing quantities of rows in a query

Navigation

#1 by (1 votes)

0

On my system I have 3 tables:

-Table Movie - A movie featuring multiple artists

-Tabela Artist

-Table Participates Movie - This table contains the ID's of the movie and artist table.

Then when I want to select a movie with all your artists, I do the following:

select ar.*, f.* from filme as f inner join participa_filme as pf on pf.id_filme = f.id_filme inner join artista as ar on ar.id_artista = pf.id_artista

Here he returns me, all the titanic movie artist

My question is, do I have to return only one titanic movie record with all its artists? without creating a line for every movie artist.

sql postgresql

asked by anonymous 04.03.2017 / 14:38

1 answer

Admob - ads in videos Non-Angular Class 2

score 1 · Accepted Answer

PostgreSQL 9.0 or newer you can use as follows:

select f.*, string_agg(ar.nome, ', ') as artistas
from filme as f 
    inner join participa_filme as pf 
        on pf.id_filme = f.id_filme 
    inner join artista as ar 
        on ar.id_artista = pf.id_artista
group by f.id_filme, f.titulo -- Aqui você vai por todos os campos que vc quer agrupar de filme

PostgreSQL 8.4 or newer you can use this way:

select f.*, array_to_string(array_agg(ar.nome, ', ')) as artistas
from filme as f 
    inner join participa_filme as pf 
        on pf.id_filme = f.id_filme 
    inner join artista as ar 
        on ar.id_artista = pf.id_artista
group by f.id_filme, f.titulo -- Aqui você vai por todos os campos que vc quer agrupar de filme