SELECT unique values from table with multiple columns containing similar data

Question

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!

Why is ARG in your desired result set? isn't it present twice in your table? — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Oct 17, 2014 at 14:41

wvdz · Accepted Answer · 2014-10-17 14:52:20Z

2

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;

edited Oct 17, 2014 at 14:52

answered Oct 17, 2014 at 14:42

wvdz

16.2k4 gold badges60 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Bollinger · Accepted Answer · 2014-10-17 14:45:06Z

1

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
  )

answered Oct 17, 2014 at 14:45

John Bollinger

190k11 gold badges102 silver badges204 bronze badges

Collectives™ on Stack Overflow

SELECT unique values from table with multiple columns containing similar data

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related