selecting distinct SET values as a single mysql column?

Question

Let's say I have id ( primary key column) and val column (of type SETwith some allowed values):

set('a','b','c','d','e','f')

I need top select these values as a column, with first column being id

+-----+------+
| id  | val  |
+-----+------+
| 102 | 'a'  |
| 102 | 'e'  |
| 102 | 'f'  |

Not sure how can this be achieved..

select id, ???? from table where id = 102;

You should probably do away with your `SET` and [normalize](http://en.wikipedia.org/wiki/Database_normalization) your data with a separate table that relates `id` in this table to the contents of your existing `SET`: i.e. this new table will look a lot like your desired output above. — eggyal, May 09 '12 at 18:43
Possible duplicate: http://stackoverflow.com/questions/1096679/can-mysql-split-a-column — Madbreaks, May 09 '12 at 18:45
@eggyal: i'm ssssoooooo don't want to do that:) ... But yeah - that would work. — Stann, May 09 '12 at 18:51

score 0 · Answer 1 · answered May 09 '12 at 18:57

As mentioned in my comment above, you probably should normalize your data so that this issue goes away entirely.

However, with the status quo, one would need to join with a table that contains all of the possible SET values where such value is in the record's SET; you can either maintain a permanent copy of that table, or else construct it dynamically with a UNION subquery:

SELECT my_table.id, set_options.val
FROM my_table JOIN (
            SELECT 'a' AS val
  UNION ALL SELECT 'b' UNION ALL SELECT 'c' UNION ALL SELECT 'd'
  UNION ALL SELECT 'e' UNION ALL SELECT 'f'
) AS set_options ON FIND_IN_SET(set_options.val, my_table.val) > 0
WHERE id = 102

selecting distinct SET values as a single mysql column?

1 Answers1