List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:July 2 1999 6:17am
Subject:Re: finding distinct SET entries
View as plain text  
On Do, 1999-07-01 18:10:54 +0100, Christof Damian wrote:
> I got a table which contains a set, it looks something like this:
> 
> col1,col2
> -------------
> aaa,(a,b)
> bbb,(e)
> ccc,(a,d)
> ddd,(a)
> 
> now I need a select which gives me:
> 
> a
> b
> d
> e
> 
> SELECT BIT_OR(col2) gives me the result, but only as binary value and
> there is no way to get back to the set values (or is there?).

You could go quite a different way by creating a second table with
only one field of this SET type holding one record for each set
element.
  col
  ___
   a
   b
   c
   d
   e

And then:
 SELECT data.col1, theset.col
 FROM   datatable, theset
 WHERE  FIND_IN_SET(theset.col, data.col2) > 0;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
finding distinct SET entriesChristof Damian1 Jul
  • finding distinct SET entriesMichael Widenius1 Jul
  • Re: finding distinct SET entriesChristof Damian2 Jul
    • Re: finding distinct SET entriesMichael Widenius2 Jul
  • Re: finding distinct SET entriesMartin Ramsch2 Jul