mysql - Return id where one of two fields matches a value, but only the field other than that vaue -

i have messages table has both to_id , from_id given message.

i'd pass user_id query , have return non-duplicate list of id's, representing users has message to/from supplied user_id.

what have right (using 12 target user_id) :

select from_id, to_id  messages   from_id = 12  or  to_id = 12

this return records user_id exists, i'm not sure how have return non-duplicates, , 1 field user_id not 12.

in short, return id's of users user 12 has existing message record with.

i hope have explained enough, have believe it's relatively simple have not learned yet.

edit :

i should have specified while current sql has 2 fields, want 1 field returned --- contact_id. , there should no duplicates.

contact_id not field in messages table, field name i'd query return, regardless of whether returning from_id or to_id

you can use union:

select  from_id contact_id messages to_id = 12 union select  to_id messages from_id = 12

also, the documentation (emphasis mine):

the default behavior union duplicate rows removed result.

alternatively, use if() (or case):

select distinct  if(from_id = 12, to_id, from_id) contact_id messages from_id = 12 or to_id = 12

Share