MYSQL Distinct for two column

Question

I got two columns: sender, receiver in my table. I want to get the distinct for both column where sender or receiver equal to b and then concat them into string.

....................
sender  |  receiver
....................
  a            b
  c            b
  b            a
  b            c
  d            f
  b            e
  b            e
  e            b

The result set should look like a,b,c,e

What should I do to achieve this?

If you concat them , you have two letters, so the result `a,b,c,e` would be two pairs. The second pair doesn't contain `b`, so I'm curious how you get that result... — GolezTrol, Nov 05 '15 at 14:44
Also, this can probably be done in SQL. Are you looking for a MySQL solution? If so, please remove the PHP tag, since it would be irrelevant for the query itself. — GolezTrol, Nov 05 '15 at 14:46
To put a closure to your question, please mark any one of the answers as accepted if they helped you. — zedfoxus, Nov 05 '15 at 22:26

score 1 · Answer 1 · edited May 23 '17 at 11:44

1

Take a look at this thread : MySQL SELECT DISTINCT multiple columns

SELECT DISTINCT value FROM
( 
    SELECT DISTINCT a AS value FROM my_table
    UNION 
    SELECT DISTINCT b AS value FROM my_table
    UNION 
    SELECT DISTINCT c AS value FROM my_table 
) AS derived

edited May 23 '17 at 11:44

Community

1
1

answered Nov 05 '15 at 14:50

score 1 · Accepted Answer · answered Nov 05 '15 at 14:57

You can just do 2 unioned queries to get the values. Union should eliminate the duplicates. Then you can use that as a sub query and do a GROUP_CONCAT on the results:-

SELECT GROUP_CONCAT(aCol)
FROM
(
    SELECT sender AS aCol
    FROM SomeTable
    WHERE sender = 'b'
    OR receiver = 'b'
    UNION
    SELECT receiver AS aCol
    FROM SomeTable
    WHERE sender = 'b'
    OR receiver = 'b'
) sub0

score 1 · Answer 3 · answered Nov 05 '15 at 14:58

Try this:

create table test (sender char(1), receiver char(1));    
insert into test values ('a','b'), ('c','b'), ('b','a'), ('b','c'), ('d','f'), ('b','e'), ('b', 'e'), ('e', 'b');

select group_concat(sender  separator ',') as result 
from (
  select sender from test where receiver = 'b' 
  union
  select 'b'
) alias;

+---------+
| result  |
+---------+
| a,c,e,b |
+---------+

If you want alphabetical ordering, you can replace select 'b' with select 'b' order by sender and you will get a,b,c,e as the result

MYSQL Distinct for two column

3 Answers3