rooms, PostgreSQL - rextester

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

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'); -- 5 is from users table.

-- 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:
-- room_members list of 'My Room' (Which is only Mike at the moment)
-- My Room's owner in case of he didnt add himself to 'room_members' table. Because he is the (Which is April)
-- Plus, admin member (Which is Pratha)
-- And this should be unique. For example, if user add himself to room_members and also owner it should fetch 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 couldnt use group by here either.
-- Also i dont need the all fields. Just room_members table fields only.


absolute service time: 0,4 sec