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?
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';
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments