Reducing line quantities in a query

Asked

Viewed 42 times

2

In my system I have 3 tables:

-Table Film - a film features several artists

-Artista Table

-Table Participates in film - this table, contains the ID’s of the movie and artist table.

When I want to select a film with all its 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

Ai he returns me, all artist of the movie Titanic

inserir a descrição da imagem aqui

my question is, is there any way I can return just one Titanic movie record with all its artists ? without creating a line for each movie artist.

sql postgresql

  • You want to create a column with all artists, ex: Leonardo, Kate Winslet, etc

  • That’s right @Jefersonalmeida

  • Which database you are using, the answer will be different depending on which you are using?

  • I’m using Postgresql, because that’s the way it is, in my system it’s creating an object for each artist, and I want it to create just one movie object from all its artists. @Jefersonalmeida

1 answer

3


In the Postgresql 9.0 or later 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

In the Postgresql 8.4 or later you can use this other 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

Browser other questions tagged sql postgresql

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