Thanks for the responses. I got one earlier that did the trick. I wanted
to try as hard as i could from altering the table, as i was already using
the Y value in other reports...and here is a great solution.
this may help someone else. so I wanted to post it. I'll use it again, i'm
sure
SELECT if( ENUM1 = 'y', 1, 0 ) + if( ENUM2 = 'Y', 1, 0 ) + if(ENUM3 = 'Y',
1, 0 ) + if(ENUM4 = 'Y', 1, 0 ) + if(ENUM5 = 'Y', 1, 0 ) AS NumHits,
{other values to select}
HAVING NumHits > 1
Thanks again Rick.
"John W. Holmes"
<holmes072000@ch To: <[EMAIL PROTECTED]>,
<[EMAIL PROTECTED]>
arter.net> cc:
Subject: RE: [PHP-DB] enum and bad
planning
10/01/2002 03:19
PM
Please respond
to holmes072000
> I have five enum fields, all Y/N. There is now(last minute) a
requirment
> to
> report on all clients that said Y to more then one of the
questions(its a
> survey). Had i known about this requirement, i could have made them
BIT
> and had a simple 1/0 option and summed the fields to check the
total....
>
> select * from table where sum(ENUM1, ENUM2,ENUM3, ENUM4, ENUM5) > 1;
//or
> something like that...
>
> anyone have an idea of how i could accomplish the same with Y/N or
should
> i
> modify the tables and change them to 1/0's which is a doable option.
Well, you could fix your "bad planning" by just altering your table.
Create a new column, then use
UPDATE your_table SET new_column = 0 where old_column = 'N';
Set it to one for 'Y', drop the old column, rename the new column, and
your done. Well, you'd have to do it for five columns, but it would
"fix" it at least.
Or you could use something like this:
SELECT * FROM your_table WHERE
LENGTH(REPLACE(CONCAT(enum1,enum2,enum3,enum4,enum5),'N','')) > 1)
That'll join all the enums together, like 'YNYNN', replace all N with an
empty string, resulting in 'YY', and then make sure the length is more
than one, i.e. there are more than two 'Y' in the string.
Hope that helps.
---John Holmes...
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
