SQL statement complicated INNER JOIN

In my database I have two tables:

user, columns: id, username
friends, columns: this_friend_id, that_friend_id

On my website, users can send each other friend requests, and when one user accepts the friend request of an other user, an entry in the friends table is made with the id of the user who sent the request in the column this_friend_id and another entry in the column that_friend_id with the user who accepted the friend request.

When a user is logged in to the page, the user should be able to see all users in a table, with all the users and whether he/she is a friend with him/her or not. But I’m not able to accomplish the second thing. It shouldn’t matter whether the logged in user is in the his_friend_id column or in the that_friend_id column.

I want to select all friends of the user.

Here is what I’ve tried so far:
(for demonstration purposes I’ve picked the id = 3 for the user)

SELECT user.id, friends.this_friend_id, friends.that_friend_id
FROM friends
INNER JOIN user
ON friends.this_friend_id = user.id OR friends.that_friend_id = user.id
WHERE friends.this_friend_id = 3 OR friends.that_friend_id = 3;

What am I doing wrong?

Edit: I changed the order of the JOIN and the WHERE statement.

>Solution :

So you’ve got the order of operators the wrong way around. The operator order of a simple query should be:

  • SELECT
  • FROM
  • JOIN
  • WHERE
  • ORDER

So your query would turn into:

SELECT user.id, friends.this_friend_id, friends.that_friend_id 
FROM friends 
INNER JOIN user 
ON friends.this_friend_id = user.id 
OR friends.that_friend_id = user.id;
WHERE friends.this_friend_id = 3 
OR friends.that_friend_id = 3

However using or statements is not great as it causes full scans of tables. If you wanted to create a more performant script you could do the below, where you use the union statement instead of the or to get this_friend_id and that_friend_id join.

SELECT user.id, friends.this_friend_id, friends.that_friend_id 
FROM [friends] 
INNER JOIN [user] 
ON friends.this_friend_id = user.id 
WHERE friends.this_friend_id = 3 
union
SELECT user.id, friends.this_friend_id, friends.that_friend_id 
FROM [friends] 
INNER JOIN [user] 
ON friends.that_friend_id = user.id 
WHERE friends.that_friend_id = 3

sqlTechHead
My technical instagram and blog with increasing content is below. This does and will have good posts to hopefully help myself learn as well as the technical community learn as well.
Instagram: https://www.instagram.com/sqltechhead/
Blog: https://sqltechhead.co.uk/

Leave a Reply