The problem
I'm trying to make a page for a website (with PostgreSQL database) that allows the owner of a group to add new members from their friends list.
Given the owner_id
of a Group
its group_id
I need to retrieve only the user_id
s from users who are both friends with the owner, and not in the group.
The relations being queried
I need to use the following relations
UserFriend
: Stores twouser_id
s to connect those two users from theUser
relation to each other as friend.UserGroup
: Stores auser_id
andgroup_id
to connect theGroup
relation to its members in theUser
relation.
The two relations are described as follows:
UserFriend
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
user_id_a | integer | | not null |
user_id_b | integer | | not null |
UserGroup
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
user_id | integer | | not null |
group_id | integer | | not null |
My attempt so far with example data
For example, given the data below, I want to find all users that are friend with user 3, and not in group 3.
SELECT user_id_a, user_id_b FROM UserGroup UG, UserFriend UF
WHERE (UF.user_id_a = 3 AND UF.user_id_b != UG.user_id)
OR (UF.user_id_b = 3 AND UF.user_id_a != UG.user_id)
AND UG.group_id = 3 GROUP BY user_id_a, user_id_b;
Expected Result
user_id_a | user_id_b
-----------+-----------
1 | 3
Even better if it could just show something like
possible_members
------------------
3
Actual Result
user_id_a | user_id_b
-----------+-----------
1 | 3
3 | 2
3 | 4
Example data
UserFriend
user_id_a | user_id_b
-----------+-----------
1 | 2
1 | 3
1 | 4
3 | 4
3 | 2
UserGroup
user_id | group_id
---------+----------
1 | 1
2 | 1
3 | 1
4 | 1
1 | 2
2 | 3
3 | 3
4 | 3
Analysis
I'm fairly certain that the issue has something to do with the way I'm handling the fact that the user_id
I'm trying to match could be in either column of UserFriend
, but I'm not sure how to fix it. I've also tried using JOIN
s without any luck.
Best Answer
Use set operations. If the user is 42, and the group is 1001, that would be
UNION
creates a result set that is the union of two query results and removes duplicate rows.EXCEPT
returns all result rows from the left side that do not occur on the right side.