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 I have following section to display :

Find a friend

Here I need to display suggetion for friend. I need to display all record from user_profile table but user should not be a friend(status should not be 2) or request should not be pending(status should not be 1).

So if I logged in as Rohan(id : 2) it should suggest me Mohan as friend suggestion.

CodePudding user response：

Would this work:

SELECT
 u.* 
FROM
 user_profile u
 LEFT JOIN user_request_table r ON u.id=r.to_user_id AND r.from_user_id = <your_logged_in_user_id>
WHERE
 r.status NOT IN (2,1) --filter pending and accepted here ; 

CodePudding user response：

This solution is for MSSQL SERVER, you can modify as per your preferred server

Pass your logged in id to declared variable

DECLARE @LoginId AS INT
SET @LoginId=2 /*here I passed logged in id as 2*/
SELECT  * FROM user_profile up 
WHERE 1=1 and id<>@LoginId
AND id NOT IN (SELECT from_user_id FROM user_request_table WHERE to_user_id=@LoginId and STATUS in(1,2)) 
AND id NOT IN (SELECT to_user_id FROM user_request_table WHERE from_user_id=@LoginId and STATUS in(1,2))

CodePudding user response：

So you want those records in user_profile table that do not have a record in user_request table where user is either from_user_id or to_user_id. The pattern is to do a left join to user_request and filter out those where user_request.id is null:

select
    p.*
from
    user_profile as p
    left join user_request as r on
        (
            (p.id = r.from_user_id and r.to_user_id = {id})
        or
            (p.id = r.to_user_id and r.from_user_id = {id})
        )
        and r.status in (1,2)
where
    r.id is null
    and p.id <> {id}
;

with {id} a parameter for the user you want to suggest friends for.

CodePudding user response：

First get all possible pairs from the user_profile table. Next filter through all the pairs and eliminate the unqualified using data from the user_request_table. It will get all the eligible (from_user , to_user)pairs.

select u1_id as fromuser,u2_id as touser
    from
        (select distinct u1.id as u1_id,u2.id as u2_id from user_profile u1 cross join user_profile u2 where u1.id!=u2.id) all_pair
    where not exists
        (select from_user_id ,to_user_id from user_request_table where status>0 and (
                ((u1_id=from_user_id) and(u2_id=to_user_id))  or   ((u1_id=to_user_id) and(u2_id=from_user_id))
            )
        )
;

The result set looks like this:
fromuser, touser
3 2
2 3