MySQL get list of unique items in a SET

Question

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: especialidad 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')

Answer 1

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])

Answer 2

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".