Get room members, room's owner and admin at the same time in one query with grouped by id (unique) on PostgreSQL 12

Question

I want to get room's member list, room's owner member in case of he doesn't exists in other table and admin member at the same time. Currently i fetch them individually.

CREATE TABLE public.room_members (
  id               bigint NOT NULL,
  member_id        bigint,
  room_id       bigint,
  group_id  bigint
);


CREATE TABLE public.rooms (
  id               bigint NOT NULL,
  member_id        bigint,
  group_id  bigint,
   name varchar(128)
);


CREATE TABLE public.members (
  id               bigint NOT NULL,
  group_id  bigint,
  username varchar(128),
    is_admin bool default false
);

CREATE TABLE public.groups (
  id               bigint NOT NULL,
  name varchar(128)
);


-- My Group created
INSERT INTO "groups" (id, name) VALUES (1, 'My Group');

-- Create users for this group. We have 4 users/members
INSERT INTO "members" (id, group_id, username, is_admin) VALUES (1, 1, 'Pratha', true);
INSERT INTO "members" (id, group_id, username) VALUES (2, 1, 'John');
INSERT INTO "members" (id, group_id, username) VALUES (3, 1, 'Mike');
INSERT INTO "members" (id, group_id, username) VALUES (4, 1, 'April');

-- April creates a room and he is owner of this room
INSERT INTO "rooms" (id, group_id, member_id, name) VALUES (1, 1, 4, 'My Room'); -- 4 is April

-- April also adds Mike to the room members. But she does not add herself. As she is owner.
INSERT INTO "room_members" (id, group_id, room_id, member_id) VALUES (1, 1, 1, 3); -- 3 is Mike

What I want is:

1. room_members list of 'My Room' (Which is only Mike at the moment)
2. My Room's owner in case of he didn't add himself to room_members table. Because he is the owner of that room (Which is April)
3. Plus, admin member (Which is Pratha)

And this should be unique. For example, if user add himself to room_members and also owner then it should fetch member one time only.

What I tried so far?

select * from members
left outer join room_members cm on cm.member_id = members.id
left outer join rooms c on c.id = cm.room_id
where c.name = 'My Room' or members.id = 1

I couldn't use group by here either. Also i don't need the all fields. Just room_members table fields only.

See here: https://rextester.com/XWDS42043

Expected output for room_members:

+-------------+------------+------------+
|  member_id  |  group_id  |  username  |
+-------------+------------+------------+
|      1      |      1     |   Pratha   |
+-------------+------------+------------+
|      3      |      1     |    Mike    |
+-------------+------------+------------+
|      4      |      1     |    April   |
+-------------+------------+------------+

• Pratha: Because he is ADMIN
• Mike: Because he is member of My Room. MEMBER
• April: Because she created that room. OWNER

room_members can be many. I just added only Mike but it can have multiple members including admins and owners.

Answer 1

You can address this with UNION:

-- list the admin(s) of the room group
select m.id, m.group_id, m.username 
from rooms r 
inner join members m on m.group_id = r.group_id and m.is_admin = true
where r.name = 'My Room'
union
-- list the members of the room
select m.id, m.group_id, m.username 
from rooms r 
inner join room_members rm on r.id = rm.room_id
inner join members m on rm.member_id = m.id
where r.name = 'My Room'
union
-- recover the room owner
select m.id, m.group_id, m.username 
from rooms r 
inner join members m on r.member_id = m.id
where r.name = 'My Room'

UNION eliminates duplicates accross queries, so if a user is both member and/or group admin and/or owner of the room, they will only appear once.

In your fiddle, this query returns:

id  group_id    username
1   4   1       April
2   3   1       Mike
3   1   1       Pratha