> 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

Reply via email to