How to get from multiple column

Question

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

Does this answer your question? [How does MySQL CASE work?](https://stackoverflow.com/questions/3389713/how-does-mysql-case-work) — Pranav Hosangadi, Aug 17 '20 at 18:26

forpas · Accepted Answer · 2020-08-17T18:23:08.957

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 |

edited Aug 17 '20 at 18:23

answered Aug 17 '20 at 18:18

forpas

160,666
10
38
76

My where condition is correct. Have you checked my expected output? – Aug 17 '20 at 18:20
Can you explain this - send_user_id + to_user_id - 5 ? – Aug 17 '20 at 18:29
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. – forpas Aug 17 '20 at 18:31

How to get from multiple column

1 Answers1