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

$sess_id = $_SESSION['user']['id'];

Relationship table:

id      u1_fk    u2_fk     u_action     status
-------------------------------------------------
 1       1        2            2          5
-------------------------------------------------
 1       3        1            3          5

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:

user_id    user_username          user_picture
-------------------------------------------------
 1           someusername           me.jpg       
-------------------------------------------------
 2           anotherusername      another.jpg
-------------------------------------------------
 3           thirdusername         third.jpg

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

$sql = "SELECT t1.id as id, "
        . " t1.u1_fk as u1, "
        . " t1.u2_fk as u2, "
        . " t2.user_id as userid, "
        . " t2.user_username as username, "
        . " t2.user_picture as picture "
        . " FROM relationships t1 "
        . " INNER JOIN users t2 "
        . " ON t1.u_action = t2.user_id "
        . " WHERE (t1.u1_fk = :user_one "
        . " OR t1.u2_fk = :user_two) "
        . " AND t1.relationship_status = 5 ";

$stmt = $dbCon->prepare($sql);
$stmt->bindParam(":user_one", $sess_id);
$stmt->bindParam(":user_two", $sess_id);
$stmt->execute();
$count = $stmt->rowCount();
if ($count > 0) {
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

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:

id      u1_fk    u2_fk      userid     username         picture
------------------------------------------------------------------
 1       1        2            2     anotherusername   another.jpg
------------------------------------------------------------------
 1       3        1            3      thirdusername     third.jpg

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

id      u1_fk    u2_fk      userid     username         picture
------------------------------------------------------------------
 1       1        2            2     someusername   me.jpg
------------------------------------------------------------------

Answer

The following query should do the trick:

SELECT
    r.id as id,
    r.u1_fk as u1,
    r.u2_fk as u2,
    u2.user_id as userid,
    u2.user_username as username,
    u2.user_picture as picture
FROM 
    users u1
    INNER JOIN relationship r 
        ON  r.status = 5
        AND (r.u1_fk = u1.user_id OR r.u2_fk = u1.user_id)
    INNER JOIN users u2
        ON  u2.user_id <> u1.user_id
        AND (r.u1_fk = u2.user_id OR r.u2_fk = u2.user_id) 
WHERE 
    u1.user_id = :sessid

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:

| id  | u1  | u2  | userid | username        | picture     |
| --- | --- | --- | ------ | --------------- | ----------- |
| 1   | 1   | 2   | 2      | anotherusername | another.jpg |
| 1   | 3   | 1   | 3      | thirdusername   | third.jpg   |

Advertisement

Answer