GROUP CONCAT and LEFT OUTER JOIN to split

I’m working with SQL Workbench. This is an example of the tables I have:

SITES
id

BUILDINGS
id
site_id

LEVELS
id
building_id

I have not created this DB and can’t modify it. Each building corresponds to a site, so it contains a KEY to such site. Same with levels and buildings.

I am trying to get this result

site_id   |   buildings_id   |   levels_id
1         |   15, 16         |   (213, 214), (313, 314)
2         |   21, 22, 23     |   (350), (400, 401, 402), (501)
3         |   31, 32, 33, 34 |   (5001, 5002), (7001), (8009), (8400)

the first two columns are easy, but I’m having issues with the 3rd. The parenthesis are illustrative, I don’t necessarily need those. But I need a way to split the string into levels by building_id.

Each (…) corresponds to the levels in one building.

This is the query I have working now which returns the first and second column. I have tried adding another LEFT OUTER JOIN to the query, but didn’t work. I tried many things but most of the things I try return errors or weird outputs.

SELECT 
    sites.id AS site_id,
    GROUP_CONCAT(DISTINCT buildings.id
        SEPARATOR '; ') AS buildings_id
FROM
    sites
        LEFT OUTER JOIN
    users_has_sites ON users_has_sites.sites_id = sites.id
        LEFT OUTER JOIN
    users ON users.id = users_has_sites.users_id
        LEFT OUTER JOIN
    buildings ON buildings.sites_id = sites.id
WHERE
    users.id = 42
GROUP BY site_id;

Any help is appreciated. Thanks

Answer

You need two levels of aggregation: one by building_id and another by site_id.

with b_l as (
  select
    b.id as building_id
    , b.site_id
    , concat('('
      , group_concat(
          l.id
          order by l.id
          separator ','
        )
      , ')'
    ) as building_levels
  from buildings as b
    left join levels as l
      on b.id = l.building_id
  group by b.id, b.site_id
)
, s_b as (
  select
    s.id
    , group_concat(
        b_l.building_id
        order by b_l.building_id
        separator ','
      ) as buildings
    , group_concat(
        b_l.building_levels
        order by b_l.building_id
        separator ','
      ) as levels
  from sites as s
    left join b_l
      on s.id = b_l.site_id
  group by s.id
)
select *
from s_b

id | buildings | levels                 
-: | :-------- | :----------------------
 1 | 1,2,3     | (1,2),(3,4,5),(6)      
 2 | 4,5,6     | (7,8),(9),(10,11)      
 3 | 7,8,9     | (12,13,14),(15,16),(17)
 4 | 10        | null                   

Or the same with lateral join:

select
  s.id
  , group_concat(
      b_l.building_id
      order by b_l.building_id
      separator ','
    ) as buildings
  , group_concat(
      b_l.building_levels
      order by b_l.building_id
      separator ','
    ) as levels
from sites as s
  left join lateral (
    select
      b.site_id
      , b.id as building_id
      , concat('('
          , group_concat(
              l.id
              order by l.id
              separator ','
            )
          , ')'
        ) as building_levels
    from buildings as b
      left join levels as l
        on b.id = l.building_id
    group by b.id, b.site_id
  ) b_l
    on s.id = b_l.site_id
group by s.id

db<>fiddle here