CODEWITHSUNDEEP
mysqlsql
qweqwe qwe
qweqwe qwe

Reputation: 393

sql distinct, getting 2 columns

I'm trying to create a simple message feature for my website but I couldn't get distinct datas from 2 columns (**from** column and **to** column)

enter image description here

you see examples datas on the picture

how can I get the return "1,4,23,45,345"?

Upvotes: 1

Views: 177

Answers (2)

dani herrera
dani herrera

Reputation: 51715

You should to union both columns and then filter for distinct values:

select distinct T.from_to from
( select `from` as from_to
  from messages
  union
  select `to` as from_to
  from messages
) T

if you really need all in a comma separate string, use GROUP_CONCAT([DISTINCT] aggregation function.

EDITED:

You should mark as solution Gerald answer. After test union operator and reread mysql union operator documentation, by default, mysql filter for distinct values:

mysql> create table ta( a int );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into ta values (1),(1),(2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from ta
    -> union
    -> select * from ta;
+------+
| a    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

then, the final query is:

  select `from` as from_to
  from messages
  union distinct
  select `to` as from_to
  from messages

Notice that distinct is not mandatory.

Only if you need a comma sparate string the first solution is necessary:

select distinct GROUP_CONCAT( distinct T.from_to from )
( select `from` as from_to
  from messages
  union
  select `to` as from_to
  from messages
) T

Upvotes: 7

Gerald P. Wright
Gerald P. Wright

Reputation: 796

select from as ft from messages
union
select to as ft from messages

In MySQL union selects distinct rows by default. You would use UNION ALL to allow for duplicates.

Upvotes: 4

Related Questions