Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table class
(
  identifier int primary key
);

create table person
(
  identifier int primary key,
  class_identifier int references class
);

create table room
(
  identifier int primary key,
  class_identifier int references class
);

create index on person(class_identifier);
create index on room(class_identifier);

insert into class values (1), (2), (3), (4), (5);
insert into person values (1, 1), (2, 1), (3, 1), (4, 2), (5, 3), (6, 4), (7, 4), (8, 5);
insert into room values (1, 1), (2, 2), (3, 2), (4, 2), (5, 3), (6, 4), (7, 5);
5 rows affected

8 rows affected

7 rows affected

select    class.identifier, array(select person.identifier from person where person.class_identifier = class.identifier) as persons
from      class
order by  class.identifier;


identifier
persons


1
{1,2,3}

2
{4}

3
{5}

4
{6,7}

5
{8}


select     class.identifier, array_agg(person.identifier) as persons
from       class
left join  (
             select  person.identifier, person.class_identifier
             from    person
           ) as person
on         class.identifier = person.class_identifier
group by   class.identifier
order by   class.identifier;


identifier
persons


1
{3,2,1}

2
{4}

3
{5}

4
{7,6}

5
{8}


select    class.identifier, array(select person.identifier from person where person.class_identifier = class.identifier) as persons,
                            array(select room.identifier from room where room.class_identifier = class.identifier) as rooms
from      class
order by  class.identifier;


identifier
persons
rooms


1
{1,2,3}
{1}

2
{4}
{2,3,4}

3
{5}
{5}

4
{6,7}
{6}

5
{8}
{7}


select     class.identifier, array_agg(person.identifier) as persons
                           , array_agg(room.identifier) as rooms
from       class
left join  (
             select  person.identifier, person.class_identifier
             from    person
           ) as person
on         class.identifier = person.class_identifier
left join  (
             select  room.identifier, room.class_identifier
             from    room
           ) as room
on         class.identifier = room.class_identifier
group by   class.identifier
order by   class.identifier;


identifier
persons
rooms


1
{3,2,1}
{1,1,1}

2
{4,4,4}
{4,3,2}

3
{5}
{5}

4
{7,6}
{6,6}

5
{8}
{7}