CODEWITHSUNDEEP
mysqlsqlselectdistinct
Matt
Matt

Reputation: 1407

SELECT unique values from table with multiple columns containing similar data

I'm trying to select a list of distinct country codes from a table that has 3 different columns that contain country code data.

Example

  Raw                                         Desired

+-----------+-----------+-----------+       +-----------+
| country_1 | country_2 | country_3 |       | countries |
+-----------+-----------+-----------+       +-----------+
| GBR       | GBR       | IRL       |       | ARG       |
| DEU       | FRA       | CHE       |       | AUS       |
| CHN       | CHN       | IND       |       | BRA       |
| USA       | CAN       | MEX       |       | CAN       |
| AUS       | JAP       | KOR       |       | CHE       |
| BRA       | ARG       | ARG       |       | CHN       |
| NOR       | SWE       | FIN       |       | DEU       |
| GBR       | FRA       | GBR       |       | FIN       |
| IRL       | IRL       | IRL       |       | FRA       |
+-----------+-----------+-----------+       | GBR       |
                                            | IND       |
                                            | IRL       |
                                            | JAP       |
                                            | KOR       |
                                            | MEX       |
                                            | NOR       |
                                            | SWE       |
                                            | USA       |
                                            +-----------+

The closest I've come so far is with a query like:

mysql> SELECT
        CONCAT(
            (SELECT GROUP_CONCAT(DISTINCT country_1 ORDER BY country_1) FROM tbl),
            ',',
            (SELECT GROUP_CONCAT(DISTINCT country_2 ORDER BY country_2) FROM tbl),
            ',',
            (SELECT GROUP_CONCAT(DISTINCT country_3 ORDER BY country_3) FROM tbl)
        ) AS countries;

+-------------------------------------------------------------------------------------------------+  
| countries                                                                                       |  
+-------------------------------------------------------------------------------------------------+  
| AUS,BRA,CHN,DEU,GBR,IRL,NOR,USA,ARG,CAN,CHN,FRA,GBR,IRL,JAP,SWE,ARG,CHE,FIN,GBR,IND,IRL,KOR,MEX |  
+-------------------------------------------------------------------------------------------------+

But as you can see, there are still duplicates and I'm not sure how I could get them into rows either.

Any help is appreciated!

Upvotes: 0

Views: 466

Answers (2)

wvdz
wvdz

Reputation: 16641

I'm not sure why you want to use group concat. The desired result can be accomplished easily with union, which removes duplicates automatically.

SELECT country1 AS countries FROM tab
UNION
SELECT country2 FROM tab
UNION
SELECT country3 FROM tab
ORDER BY 1;

Upvotes: 2

John Bollinger
John Bollinger

Reputation: 180058

You get duplicates because you are eliminating duplicates only within each column, not overall. You probably want something more like this:

SELECT GROUP_CONCAT(country)
  FROM (
    SELECT DISTINCT country1 AS country FROM tbl
    UNION
    SELECT DISTINCT country2 AS country FROM tbl
    UNION
    SELECT DISTINCT country3 AS country FROM tbl
  )

Upvotes: 1

Related Questions