Mysql query not return correct result

Question

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

What is the query supposed to do? What does "some condition with given userId" mean? — Barmar
– Barmar, Commented May 18, 2015 at 19:27
Your query never checks the status column, how is it supposed to tell if the user passed you? — Barmar
– Barmar, Commented May 18, 2015 at 19:46
how can i solve it now. can you give me any query logic @Barmar — Saime1953
– Saime1953, Commented May 18, 2015 at 19:50
Your sqlfiddle just returns 25. Why do you say it returns 17 and 25? Is that what you want it to return? — Barmar
– Barmar, Commented May 18, 2015 at 19:51

Barmar · Accepted Answer · 2015-05-18 20:00:24Z

2

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

answered May 18, 2015 at 20:00

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query not return correct result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related