MySQL / Find values in a set that aren't in a column

Viewed 61

I have some data which contains a comma separated list and three columns. What I'd like is to find the values that exist in the list but not in any column.

DATA:

+-------+-------+--------+-----------------+
| col_a | col_b | col_c  | list            |
+-------+-------+--------+-----------------+
| red   | NULL  | green  | yellow,blue     |
| red   | blue  | green  | red,blue,green  |
| red   | NULL  | yellow | green           |
| blue  | white | green  | red,white,green |
| NULL  | NULL  | NULL   | red,blue,green  |
+-------+-------+--------+-----------------+

DESIRED OUTPUT:

+-------+-------+--------+-----------------+----------------+
| col_a | col_b | col_c  | list            | unmatched      |
+-------+-------+--------+-----------------+----------------+
| red   | NULL  | green  | yellow,blue     | yellow,blue    |
| red   | blue  | green  | red,blue,green  |                |
| red   | NULL  | yellow | green           | green          |
| blue  | white | green  | red,white,green | red            |
| NULL  | NULL  | NULL   | red,blue,green  | red,blue,green |
+-------+-------+--------+-----------------+----------------+

At the moment I have a query that works but it's very ugly:

SELECT *,
TRIM(
    BOTH ',' FROM 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    LIST,
                    IFNULL(col_a,''),
                    ''
                ),
                IFNULL(col_b,''),
                ''
            ),
            IFNULL(col_c,'')
            ,''
        ),
        ',,',
        ','
    )
) AS unmatched
FROM color_list

Is there a cleaner way to find the unmatched values? The thing I'm doing here would be really hard to scale and feels quite awkward and fragile.

Here's a fiddle.

0 Answers
Related