CODEWITHSUNDEEP
phpmysqlsql
Slinky
Slinky

Reputation: 17

How to remove duplicated/corresponding values from table?

I have a table structure similar to this:

send_id | rec_id

It is populated with the following sample data:

id1 | id2

id2 | id1

id3 | id1

id3 | id2

id4 | id1

id1 | id4

My attempt was using this query on id1:

SELECT * from inbox WHERE (send_id="id1" OR rec_id="id1") GROUP BY send_id,rec_id

but it doesn't work correctly, instead it shows:

id1 | id2

id2 | id1

id3 | id1

id1 | id3

id4 | id1

I would like the output to be something like this:

id1 | id2

id3 | id1

id4 | id1

I can see what's going wrong, but how do I fix it?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This is a good place to use the least() and greatest() functions:

SELECT least(send_id, rec_id) as id1, greatest(send_id, rec_id) as id2
from inbox
WHERE send_id = 'id1' OR rec_id = 'id1'
GROUP BY least(send_id, rec_id), greatest(send_id, rec_id);

Or, if you prefer:

SELECT distinct least(send_id, rec_id) as id1, greatest(send_id, rec_id) as id2
from inbox
WHERE send_id = 'id1' OR rec_id = 'id1';

Upvotes: 1

Related Questions