CODEWITHSUNDEEP
mysql
Popescu Marian
Popescu Marian

Reputation: 93

mysql query unite 2 columns

I have a table friends with 4 columns (id, sender, receiver, status) and I need a query that will unite (reunion, I dont know the word in english) the sender and receiver colums. For example the table looks like this

| sender | receiver |
|    2   |   10     |
|    2   |    8     |
|    2   |    9     |
|    6   |    2     |
|    7   |    3     |

And the query should give only the unique ids from both sender and receiver. (2,3,6,7,8,10)

Upvotes: 0

Views: 266

Answers (2)

Matt Fenwick
Matt Fenwick

Reputation: 49085

This sounds like a job for union:

select sender as id from <table_name>
union
select receiver as id from <table_name>

Note that union will remove duplicates (use union all if you want to keep duplicates).

Upvotes: 4

John Woo
John Woo

Reputation: 263693

SELECT DISTINCT a.iResult
FROM
    (SELECT sender as iResult FROM tableName
        UNION
     SELECT receiver as iResult FROM tableName) a
ORDER BY iResult ASC

Returns 

2
3
6
7
8
9
10

OR

SELECT GROUP_CONCAT(b.iResult)
     (SELECT DISTINCT a.iResult
     FROM
             (SELECT sender as iResult FROM tableName
                 UNION
              SELECT receiver as iResult FROM tableName) a
         ORDER BY iResult ASC) b

returns

2,3,6,7,8,9,10

Upvotes: 2

Related Questions