Modify the response of a SQL query involving junction table

Question

I have two tables linked in a junction table like this :

REATE TABLE eventsUsers (
    eventId INT REFERENCES events (eventId),
    userId INT REFERENCES users (userId),
    PRIMARY KEY (eventId, userId)
);

I want to return all the events with their user logins. So far I did the following SQL request :

    SELECT events.eventId, events.name, users.login
    FROM events
    LEFT OUTER JOIN eventsUsers ON events.eventId = eventsUsers.eventId
    LEFT OUTER JOIN users ON users.userId = eventsUsers.userId

And it returns :

 eventid |    name    | login 
---------+------------+-------
       1 | event1     | admin
       1 | event1     | user
       2 | deuxieme   | admin
       3 | troisieme3 |

But I would like it to return something like this :

 eventid |    name    | login 
---------+------------+-------
       1 | event1     | [admin, user]
       2 | deuxieme   | admin
       3 | troisieme3 |

With one line for one event and an array (or list) of the user logins.

So my question is : is it possible to do this in SQL and how?

Edit : I use Postgresql.

In mysql , need to do group by event if and use group concat to get comma separated users — radar, Oct 07 '14 at 22:34
It might be possible, but it depends on what dbms you are using. — jpw, Oct 07 '14 at 22:34

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

Take a look at this other answer that describes how to use the string_agg function to do something like what you've asked for.

So to get your result you'd want:

SELECT events.eventId, events.name, '[' || string_agg(users.login, ',') || ']'
    FROM events
    LEFT OUTER JOIN eventsUsers ON events.eventId = eventsUsers.eventId
    LEFT OUTER JOIN users ON users.userId = eventsUsers.userId
GROUP BY events.eventId, events.name

Apologies if I've mussed the syntax slightly, I'm actually a SQL Server guy.

edited May 23 '17 at 11:59

Community

1
1

answered Oct 07 '14 at 22:51

Jeff

12,555
5
33
60

The '[' + doesn't work with postgresql but removing it make it work : it returns "admin,user" and that what I needed, thanks! – Simon Oct 07 '14 at 23:09
@user3683807 Right, yeah I didn't do my research - you need `||` for concatenation in PostgreSQL – Jeff Oct 08 '14 at 00:46

Modify the response of a SQL query involving junction table

1 Answers1