MySQL 5.6 here. I have the following tables:
DESCRIBE profiles;
+----------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------+----------------+
| profile_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| profile_given_name | varchar(100) | YES | MUL | NULL | |
| profile_surname | varchar(100) | YES | MUL | NULL | |
+----------------------------+---------------------+------+-----+---------+----------------+
describe friendships;
+----------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+----------------+
| friendship_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| requester_profile_id | bigint(20) unsigned | NO | MUL | NULL | |
| recipient_profile_id | bigint(20) unsigned | NO | MUL | NULL | |
+----------------------+---------------------+------+-----+---------+----------------+
The profiles table represents users of a system, and friendships represents a many-to-many relationship of which users are friends with which other users. When a user/profile sends a "Friend Request" to a user, they are considered to be therequester in of the friendship. Conversely, those who receive Friend Requests from others are the recipients.
I'm trying to write a (seemingly) simple ANSI-compliant SELECT that tells me all the profiles a particular profile is friends with, regardless of whether they were the requester or the recipient of the friendship.
To set the DB up with some data:
INSERT INTO friendships (
friendship_ref_id,
requester_profile_id,
recipient_profile_id
) VALUES (
'01234',
2,
1
), (
'67890',
1,
3
), (
'78901',
1,
4
), (
'89012',
2,
3
), (
'90123',
3,
4
);
Then my best attempt thus far:
SELECT f.requester_profile_id,
f.recipient_profile_id
FROM profiles p
INNER JOIN friendships f
ON (
f.requester_profile_id = p.profile_id
OR
f.recipient_profile_id = p.profile_id
)
WHERE p.profile_id = 1;
When I run this I get:
+----------------------+----------------------+
| requester_profile_id | recipient_profile_id |
+----------------------+----------------------+
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
+----------------------+----------------------+
But what I really want is a single column composed of all the DISTINCT profile IDs (requester and recipient alike) that profile_id = 1 is friends with, something like:
+----------------------+
| profile_friends |
+----------------------+
| 2 |
| 3 |
| 4 |
+----------------------+
...since profile_id = 1 is friends with profile_id IN { 2, 3, 4 }, etc.
What am I missing here to make the query work?
There are two options here.
The first uses a CASE to decide which column to use for each result:
SELECT
CASE WHEN f.requester_profile_id = p.profile_id THEN f.recipient_profile_id
ELSE f.requester_profile_id END as profile_friends
FROM profiles p
INNER JOIN friendships f
/* Original ON clause would still work, but I prefer the briefer syntax */
ON p.profile_id IN (f.requester_profile_id,f.recipient_profile_id)
WHERE p.profile_id = 1;
The second UNIONs two SELECT statements together, where one checks requester and the other checks recipient:
SELECT f.recipient_profile_id as profile_friends
FROM profiles p
INNER JOIN friendships f
ON f.requester_profile_id = p.profile_id
WHERE p.profile_id = 1
UNION
SELECT f.requester_profile_id
FROM profiles p
INNER JOIN friendships f
ON f.recipient_profile_id = p.profile_id
WHERE p.profile_id = 1;