mysql - Best way to insert all the combinations -

i have been thinking quite bit , can't seem figure out way in plain mysql.

i have list of words (size not fixed):

• word11
• word21
• word31
• word41

for each 1 of words, have list of synonyms (size not fixed). word11, intance:

• word12
• word13
• word14
• word15
• word16

my goal different possibibilities:

• word11 word21 word31 word41
• word12 word21 word31 word41
• word13 word21 word31 word41
• word14 word21 word31 word41
• ...
• word11 word21 word31 word417
• word11 word21 word31 word418

i thought using temporary table store different results.

for each word, , each result stored in temporary table, insert new synonyms instead.

the first iteration word11 give

• word11 word21 word31 word41
• word12 word21 word31 word41
• word13 word21 word31 word41
• word14 word21 word31 word41
• word15 word21 word31 word41
• word16 word21 word31 word41

for iteration 2, each 1 of these entries, insert synonyms replacing word21 synonyms.

but seems tedious , there might better way (not sure close work).

any hint?

unfortunately, mysql not support recursive or hierarchical queries, needed this. fixed number of words, can do:

select w1.synonym, w2.synonym, w3.synonym, w4.synonym synonyms w1 cross join      synonyms w2 cross join      synonyms w3 cross join      synonyms w4 w1.word = 'word1' ,       w2.word = 'word2' ,       w3.word = 'word3' ,       w4.word = 'word4'; 

(in actual fact, query more complicated unless have word own synonym, easy-enough fix above query).

in order add more words, need more joins, alas. if have fixed number, can hardcode it. otherwise, can generate sql prepared statement , execute. particular sql like:

   select concat('select ', group_concat('w_', word, '.word' separator ', '),                  ' ',                  group_concat('synonyms w_', word separator ' cross join '),                  ' ',                  group_concat('w_', word, ' = ''', word, '''' separator ' , '                 )    words w;