Search code examples
sqlpostgresqlmany-to-many

SQL many to many subquery

Here is a SQLFiddle which shows my current query:

I’d prefer to display one line for each contact+location association, and group all the roles. So instead of 5 results, I would have 2 - one result for each unique location+contact combination, and all the roles of that contact combined into an array or something.

Not sure if this possible with raw SQL.

Desired results based on schema:

location 1        ryan       accountant, admin
location 2        steve      admin, manager, developer

Only locations with a contact should show.

Solution

  • Use array_agg.

    SQL Fiddle

    SELECT 
  locations."name", 
  contacts.name, 
  array_agg(roles."name") AS "Roles"
FROM locations INNER JOIN contacts ON locations."id" = contacts.location_id
   INNER JOIN "public".memberships ON "public".memberships.contact_id = contacts."id"
   INNER JOIN roles ON "public".memberships.role_id = roles."id"
 group by locations."name", contacts.name