CODEWITHSUNDEEP
phpmysqlsqldatabasedistinct
programmer
programmer

Reputation: 167

DISTINCT values for two columns in MySQL

I have a table with contents like the following:

+----+-----------+---------+--------+------+
| id | text_from | text_to |  text  | seen |
+----+-----------+---------+--------+------+
|  1 | A         | B       | Hello1 |    0 |
|  2 | X         | Y       | Hello2 |    1 |
|  3 | Y         | X       | Hello3 |    1 |
|  4 | B         | A       | Hello4 |    1 |
+----+-----------+---------+--------+------+

It is a conversation like A sends a text to B, B sends to A etc. How can I get the DISTINCT conversation? For example, distinct conversation between A and B, or X and Y etc.

I want to get something like

+----+-----------+---------+--------+------+
| id | text_from | text_to |  text  | seen |
+----+-----------+---------+--------+------+
|  1 | A         | B       | Hello1 |    0 |
|  2 | X         | Y       | Hello2 |    1 |
+----+-----------+---------+--------+------+

If once text_from and text_to has two unique values, it can not be repeated. For example, if there is text_from = A, text_to = B, the table should not have text_from = B, text_to = A.

I am trying several methods for DISTINCT and GROUP BY since a few hours, but could not figure out any solution! Any suggestions would be greatly appreciated.

Upvotes: 1

Views: 123

Answers (1)

Derek
Derek

Reputation: 23308

Seems like a simple NOT EXISTS should do the trick. Example SQL Fiddle

select *
from table t
where not exists (
  select 1
  from table t1
  where
    (
      (t.text_from = t1.text_from
       and t.text_to = t1.text_to)
      or (t.text_from = t1.text_to
       and t.text_to = t1.text_from)
    ) and t.id > t1.id
  )

Upvotes: 1

Related Questions