MySQL get list of unique items in a SET

I have a products table with a column of type SET (called especialidad), with these possible values.

[0] => NADA
[1] => Freestyle / BMX
[2] => Street / Dirt
[3] => XC / Rural Bike
[4] => All Mountain
[5] => Freeride / Downhill / Dual / 4x
[6] => Ruta / Triathlon / Pista
[7] => Comfort / City / Paseo
[8] => Kids
[9] => Playera / Chopper / Custom
[10] => MTB recreacion
[11] => Spinning / Fitness

Any given product can have one or many of these i/e "Freestyle / BMX,Street / Dirt" Given a subset of the rows, I need to get a list of all the present "especialidad" values. But I need a list to be exploded and unique

• Article1: "Freestyle / BMX,Street / Dirt"
• Article2: "Street / Dirt,Kids"
• Article2: "Kids"
• Article4: "Street / Dirt,All Mountain"
• Article5: "Street / Dirt"

I need a list like this

• Freestyle / BMX
• Street / Dirt"
• Kids"
• All Mountain"

I tried with group_concat(UNIQUE) but I get a list of the permutations...

Update: e开发者_运维百科specialidad is a column of type SET. There is NO joins to do, only one table.

Just assume the table has an ID column, a name column an a especialidad column.

especialidad: set('NADA', 'Freestyle / BMX', 'Street / Dirt', 'XC / Rural Bike', 'All Mountain', 'Freeride / Downhill / Dual / 4x', 'Ruta / Triathlon / Pista', 'Comfort / City / Paseo', 'Kids', 'Playera / Chopper / Custom', 'MTB recreacion', 'Spinning / Fitness')

You could use the BIT_OR aggregate function to find all the values that are set:

Select BIT_OR(especialidad) AS active_values
    from myTable WHERE [your selection criteria];

And then, having done that, you can write a messy conditional using concat_ws to decode the bit values of the set back to stings:

Select concat_ws(','
    if (num&1,'NADA',null),
    if (num&2,'Freestyle / BMX',null),
    if (num&4, 'Street / Dirt',null),
      ... etc ...
    ) from 
       (Select BIT_OR(especialidad) AS num from myTable 
         WHERE [selection criteria]) as t;

But I would also suggest you read this article about the SET datatype, especially the section entitled "why you shouldn't use SET".

You should let us know more about your database structure. But let's do some assumptions here.

I assume there is some ProductEspecialidad table that has ProductID and EspecialidadID.

Then Article1 has two records there => EspecialidadID = 1 and EspecialidadID = 2

So on display you select all ProductEspecialidad for a ProductID and display the text descriptions.

If you'd need a list like you described (and name of Especialidad is in column name), then you'd need to

select name from Especialidad where ID in 
  (select unique EspecialidadID  from ProductEspecialidad
   where ProductID = [your products selection conditions or nothing])