0

I have following table with data :

id  send_user_id   to_user_id   status
1      5              56           0
2      5              125          1
3      500             5           1
4      850             5           0
5      5              365          0
6      5              800          1
7      8520           156          1

Now i want to get get where send_user_id =5 or to_user_id = 5 and status = 1

I have tried below query

select * from mtable
where send_user_id = 5 and status = 1 or to_user_id = 5 and status = 1

with above i am getting following data :

  id  send_user_id   to_user_id   status
    2      5              125          1
    3      500             5           1
    6      5              800          1

but i want following output :

all_user_id
  125
  500
  800
Improve this question
1

1 Answer 1

Reset to default
1

Use a CASE expression:

select case when send_user_id = 5 then to_user_id else send_user_id end all_user_id
from mtable
where 5 in (send_user_id, to_user_id) and status = 1

And another way that works because because we're dealing with integer columns:

select send_user_id + to_user_id - 5 all_user_id
from mtable
where 5 in (send_user_id, to_user_id) and status = 1

See the demo.
Results:

> | all_user_id |
> | ----------: |
> |         125 |
> |         500 |
> |         800 |
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

My where condition is correct. Have you checked my expected output?
Can you explain this - send_user_id + to_user_id - 5 ?
In the sum send_user_id + to_user_id it's guaranteed that one of the operands is 5, so if we subtract 5 the result will be the other operand which is not 5.