Skip to content
Advertisement

How to INNER JOIN the correct VALUE from 2 values in the table where 1 is correct

I need to select and inner join the correct u1 or u2 the opposite of the current user to display the correct friend’s list with the correct picture/id

Sess id

Relationship table:

Details:

  • Id is auto increment id
  • u1_fk is the first users id the one who engaged (submitted a friend request)
  • u2_fk is the other user’s id (The one who got the friend request)
  • u_action is the last user who made an action (etc sent a friend request, denied/accepted a friend request, blocked the other user etc etc)

User table:

SQL + PHP (Here I INNER JOIN u_action on user_id):

I thought I could use the u_action, but that does only work for one of them obviously since it is either one of the 2 users ids stored in that field.

So how do I know whenever my query should inner join u1 or u2, I’m a bit confused here I hope someone can help me out, im thinking about doing 2 queries and sort all the results and remove the users own id and do a where in clause in the second querie, i am wondering however if this can be done in just 1 query.

What the result from the database should look like.

What I want as a result from the database in this particular example where sess_id is 1:

What I want as a result from the database in this particular example where sess_id is = 2

Advertisement

Answer

The following query should do the trick:

u1 represents the users record for the current session id, and u2 is JOINed in as the user on other end of the relation.

This demo on DB Fiddle with you sample data returns, for session id 1:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement