CODEWITHSUNDEEP
c#mysqljoin
Joshua Bakker
Joshua Bakker

Reputation: 2348

Joining table to other table using either one of two columns

I'm currently writing a program in C# and I want to load friendships from the database based on the id passed as function parameter.

I got 2 tables (I only display the important columns).

Table 1: players

+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| id                   | int(11)             | NO   | PRI | NULL    | auto_increment |
| username             | varchar(15)         | NO   |     | NULL    |                |
+----------------------+---------------------+------+-----+---------+----------------+

Table 2: messenger_friends

+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| user_one_id | int(11) | NO   | PRI | NULL    |       |
| user_two_id | int(11) | NO   | PRI | NULL    |       |
+-------------+---------+------+-----+---------+-------+

The thing is, my idea was the following: in messenger_friends, save one line for a friendship. I know I can save 2 for one friendship but it would mean more storage as 500 friendships would become 1000 records. Now, in my application I have to JOIN messenger_friends to players. I got this function:

public async Task<IReadOnlyList<MessengerFriend>> GetFriends(int playerId)

In here, I need to get all records from messenger_friends where EITHER user_one_id OR user_two_id is playerId. Then in the same query, I want to join it to players. I know I can get the records this way:

SELECT * FROM messenger_friends WHERE user_one_id = {playerId} OR user_two_id = {playerId}

But I'm not sure how to join it to the players table as I need to join EITHER user_one_id OR user_two_id with players.id

Upvotes: 1

Views: 56

Answers (1)

juergen d
juergen d

Reputation: 204746

SELECT * 
FROM players p
JOIN messenger_friends f ON p.id in (f.user_one_id, f.user_two_id)
WHERE p.id = {playerId}

or

SELECT * 
FROM players p
JOIN messenger_friends f ON p.id = f.user_one_id
                         OR P.id = f.user_two_id
WHERE p.id = {playerId}

Upvotes: 2

Related Questions