CODEWITHSUNDEEP
mysql
Abu Bakkar Siddique
Abu Bakkar Siddique

Reputation: 31

How to get distinct value from repeating result

I have a table like name 'items'

id      sender  receiver        item
1       1       2               computer
2       2       1               mobile
3       1       3               ipad
4       4       1               ring
5       3       1               camera
6       1       8               pc
7       5       1               ring
8       1       10              tablet

I only can do these two queries like

SELECT `receiver` FROM `items` WHERE `sender` ='1'

result

2
3
4
10

and

SELECT `sender` FROM `items` WHERE `receiver` ='1'

result

2
4
3
5

How can I get distinct recevier/sender(count only one, if comes more than 1) where sender/receiver=1

like bellow

2
3
4
5
10

Upvotes: 0

Views: 46

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can do this without union:

SELECT DISTINCT (case when receiver = 1 then sender else receiver end) 
FROM items
WHERE sender = 1 or receiver = 1

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

One option is to use UNION:

SELECT receiver FROM items WHERE sender ='1'
UNION
SELECT sender FROM items WHERE receiver ='1'

Depending on your results, you may need to use SELECT DISTINCT with each query.

(Produces slightly different results given your sample data, as I think you had some typos)

Upvotes: 3

Related Questions