How I get single data from multiple row (SMS issue)

Question

I am going to make chat option. My database are as follows--

id        from_sms       to_sms       body_sms       time
1        1000           2000          hi                timestamp
2        1000           2000          how r u?          timestamp  
3        2000           1000          fine. u?          timestamp
4        1000           2000          I am fine.        timestamp
5        3000           1000          hey r u there?    timestamp
6        1000           3000          bg now.           timestamp
7        4000           1000          I am new          timestamp

Here- 1000 = Me 2000 = X 3000 = Y 4000 = Z

Now I want to show Only number like on our mobile shows

Message
-------
2000
3000
4000

How i can execute my requirements?

Answer 1

It looks like you want to exhibit all distinct numbers a given user interacted with. This might be simpler and more efficently done with union:

select from_sms as contact from mytable where to_sms = 1000
union  -- on purpose: removes duplicates
select to_sms from mytable where from_sms = 1000

Another approach would use conditional expressions:

select distinct case when from_sms = 1000 then to_sms else from_sms end as contact
from mytable
where 1000 in (from_sms, to_sms)