Query where Sum of specified Columns (all ints) in Row are less than a specified int

I’m writing a word search program.

My database is set up to MyISAM
with one table (Words) structured

WordID | String | A | B | ... | Z |
------------------------------------
int     varchar int  int ...  int

Where the values for columns A – Z are the # of occurrences of that letter in the string.

To write a query to find all possible words made out of a specified set of characters (including wild characters) ie: "Bu!!er" should return but, butt, bull, etc

Where

 S is the set of characters specified that we can use
 W is the set of characters in a word

I’ll need to query the database for all strings where

 # of occurences in the word for each specified character (not including "!") is less than number of occurrences of that character in the specified string
W_k < S_k where k is each character specified

AND

# of occurrences of letters not specified in the specified string are in SUM less than the total occurrences of the wildcard character ("!") in the specified string 
W_q < S_! where q is each character not specified and S_! total amount of occurrences of "!".

For the first part of the WHERE statement (W_k < S_k)
For bu!!er the statement would be

 `B` <= 1 AND `U` <= 1 AND `E` <= 1 AND `R` <= 1

And for the second part

 `A` + `C` + `D` + ... + `Z` <= 2

Is there a better way to do it than this?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s