Thanks for the reply Bob,
May I ask then what is the purpose of the SET(...) datatype ?
In the past, I've always avoided it since I assumed that any
search that uses LIKE '%x%' in a query would be slow. However,
I now have a table that will never grow to more than 5,000
entries and using the SETs for 3 of the columns has made life
way easier so far, especially when creating the administration
screens for this application. 5,000 rows is very small for
a database and my queries using LIKE '%x%' have been very
fast. In fact, the only problem I've encountered is this inability
to retrieve a list of all the SET values that are used at
least once.
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).
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
