CODEWITHSUNDEEP
phpmysqlsqlmysqli
Saime1953
Saime1953

Reputation: 13

Mysql query not return correct result

I have two tables

one is user_matchs

id | user_id  | matched_user_id  |  status
36 |    17    |       24         |  passed
37 |    17    |       25         |  friend

another is users

id | name     | address |  age
17 |  mamun   | test    |   23
25 |  shihab  | test2   |   30
24 |  shihab2 | test22  |   30

Now I want to retrieve list of all users depending on some condition with given userId

suppose I am user 17. now I want to see all user whose are not my friend and whose passed me.if user_matchs table

user_id=17 and matched_user_id=24 and status=passed then it means i passed user 24 but 24 not passed me if 24 also passed me then row will be

user_id=17 and matched_user_id=24 and status=friend

another logic

user_id=24 and matched_user_id=17 and status=passed then it means user 24 passed me but i am not passed user 17 if 17 also passed me then row will be

user_id=24 and matched_user_id=17 and and status=friend

When I give userId 17 with search query, it returns no user because user 25 is friend of user 17 and user 17 already passed user 24.

But when I give userId 24 in the search query, then it returns user 17 and 25. Because user 25 is not friend or have not passed user 24 and user 17 is not friend of or user 24 not passed user 17.

I am trying with this query but it does not work correctly:here 24 is given id 

SELECT *
FROM `users`
WHERE users.id != 24 AND

        users.id NOT IN (SELECT matched_user_id
                               FROM `user_matchs`
                               WHERE user_id = '24'
                              )
and
        users.id NOT IN (SELECT user_id
                               FROM `user_matchs`
                               WHERE matched_user_id = '24'
                              )

sql fiddle

Upvotes: 1

Views: 54

Answers (1)

Barmar
Barmar

Reputation: 782717

The second subquery needs to check the status field, so it only excludes friends, not people who have passed you.

SELECT *
FROM `users`
WHERE users.id != 24 AND

    users.id NOT IN (SELECT matched_user_id
                        FROM `user_matchs`
                        WHERE user_id = '24'
                    )
and
    users.id NOT IN (SELECT user_id
                        FROM `user_matchs`
                        WHERE matched_user_id = '24'
                        AND status = 'friend'
                    )

sqlfiddle

Upvotes: 2

Related Questions