Re: how to get the union of the values in a SET column ?

[chas]

I was hoping that since each member of the SET really
represents a number, that there would be some bitwise
operation that would painlessly produce all the members
of the SET that appear at least once.

Sir, I apologize  for taking so long to respond. I was moving some 
furniture this weekend and dropped a desk on my keyboard.

No worries Bob. I've only just got back online myself (though my 
excuse is more hedonistic - was snowboarding).


You are right about SETs, and I am blind. The obvious solution is the 
bitwise or. MySQL has a summary function called Bit_Or(). 
Unfortunately, it returns a decimal representation of the result, so 
you have to massage it a little.
SELECT Bin(Bit_Or(column_name)) FROM your_table;

Excellent. I actually tried the Bit_or and Bit_and() functions
but must have screwed up on my binary conversion aaah! b/c the 
results I had didn't seem to make sense. The Bin() function is 
very handy.


The bits are in reverse order; if your SET declaration includes a, b, 
c, and d, and you columns include all but b, you'll get 1101 from the 
SELECT statement. This is fine if you're going to process the bits in 
your client. But if you want to display the bits in MySQL in the same 
order as your SET declaration, you can use
Reverse(Binary(Bin(Bit_Or(column_name
which will give you 1011.

Awesome. That's just the ticket. 


Thank you very much again,

chas


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: how to get the union of the values in a SET column ?

[chas]

Hi again Bob,

I know it's not 'proper' database design and I should have
normalized the one-to-many relationships to multiple tables.
However, it was fast and gave me a result which worked quickly
(and up against a mad deadline).

I think you've just identified the most common use of the SET datatype.

In your original posting, you said that a procedural solution 
wouldn't scale well. If you database will never be more than 5,000 
records, why are you concerned about scalability? Are you planning on 
processing a hundred concurrent queries?

I meant scalability in terms of the number of items in the SET
(not the number of rows in the table). For example, consider a
SET field with 26 items - 
eg. set_col SET ('A','B','C'. 'Z')

If I wish to find out which values of the SET have been used 
at least once, I have to either:
a) Query for each member of the SET and find out which gives a result:
   SELECT * FROM my_table WHERE set_col LIKE ('%A%')
   SELECT * FROM my_table WHERE set_col LIKE ('%B%')
   etc.
b) Select DISTINCT(set_col) and then parse the results
   for for each member of the set .. which is a worse method.

I was hoping that since each member of the SET really
represents a number, that there would be some bitwise 
operation that would painlessly produce all the members
of the SET that appear at least once. 

If not, no worries - I'll redo the database properly
when I get some time.

Thanks again,

chas



  Bob wrote:
  Sir, the basic problem is that you've violated first normal form,
  which states columns should only contain atomic (indivisible) data.
  Since you plan to divide up the data in your columns, it's obviously
  not indivisible.
  
  My recommendation is to brush up on the first three normal forms and
  redesign your tables. There may be a way to do what you want with
  MySQL functions, but that will require a lot of processing and slow
  your queries down. You'll be back where you started, with a solution
  that doesn't scale well.

  Chas  wrote:
  I have a column that is defined as a SET. For simplicity's sake,
  let's say the column is defined as:
  
  set_col SET ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',  'Z')
  
  After entering a few hundred rows, I know that some but not all
  values of the SET have been used at least once.  How can one work
  out which values have been used at least once ?
  
  Doing a SELECT DISTINCT(set_col) on the table might provide
  something like this:
  
  A,B,D
  A,C,G,N,M,P
  C,E,F,H,I,R,S
  C,E,G,I
  D,F
  G,H,L,M,O
  etc
  
  But it then requires processing to work out which
  values have been used at least once. It's not very
  scalable.
  
  Is there a method to retrieve a list of all the values
  that have been used at least once ?
  


-
Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail 
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

how to get the union of the values in a SET column ?

[chas]

I have a column that is defined as a SET. For simplicity's sake, 
let's say the column is defined as:

set_col SET ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',  'Z')

After entering a few hundred rows, I know that some but not all
values of the SET have been used at least once.  How can one work
out which values have been used at least once ? 

Doing a SELECT DISTINCT(set_col) on the table might provide
something like this:

A,B,D
A,C,G,N,M,P
C,E,F,H,I,R,S
C,E,G,I
D,F
G,H,L,M,O
etc

But it then requires processing to work out which 
values have been used at least once. It's not very
scalable.

Is there a method to retrieve a list of all the values
that have been used at least once ? 

Thank you very much,

chas

ps. using an old version at the moment - 3.22.25




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php