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:
room_members
list of ‘My Room’ (Which is only Mike at the moment)- 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) - 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.
Advertisement
Answer
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