> 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.
Advertising
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
