How to join table conditional column-CodePudding

I have following two tables :

user_profile

id | name  | email       | phone
1  | Rahul |[email protected]  |1234567890
2  | Rohan |[email protected] |1234567890
3  | Mohan |[email protected]  |1234567890

user_request_table

    id | from_user_id|to_user_id|status
    1  |      1      |   2      | 2 
    2  |      3      |   1      | 2

Here status

0 = Request is cancel, 1 = request is pending, 2 = request accepted

So request accepted(status=2) means they both are friends.

Now suppose I am login with user id 1 (Rahul) I need to display rahul's friend with user_profile data. What should be the query so it display Rohan's and Mohan's profile data.

CodePudding user response：

You should join the tables like this:

SELECT p.*
FROM user_profile p INNER JOIN user_request_table r
ON (p.id, ?) IN ((r.from_user_id, r.to_user_id), (r.to_user_id, r.from_user_id))
WHERE r.status = 2;

Change ? to the id of the user that you want.

See the demo.

CodePudding user response：

SELECT U2.ID,U2.NAME,U2.EMAIL,U2.PHONE
FROM USER_PROFILE AS U
JOIN REQUEST AS R ON U.ID=R.FROM_USER_ID
JOIN USER_PROFILE AS U2 ON R.TO_USER_ID=U2.ID
 WHERE R.STATUS=2 AND R.FROM_USER_ID=1
   UNION ALL
SELECT U2.ID,U2.NAME,U2.EMAIL,U2.PHONE
FROM USER_PROFILE AS U
JOIN REQUEST AS R ON U.ID=R.TO_USER_ID
JOIN USER_PROFILE AS U2 ON R.FROM_USER_ID=U2.ID
WHERE R.STATUS=2 AND R.TO_USER_ID=1

Could you please try this query if it is suitable for you