Just a few notes on joining a MySQL table to itself to get unique combination of data stored in its fields.
Suppose we have a MySQL table named `keywords` having fields `word1`, `word2` and `word3`. Once we have got a few records inserted into this table we would want to show all possible combination of keywords stored in those fields. For example, a select query returns a following set of data:
mysql> select * from keywords; +-------+---------+-------+ | word1 | word2 | word3 | +-------+---------+-------+ | nice | job | John | | good | looking | Smith | +-------+---------+-------+
Running the following left join (or SELF JOIN) would give all possible combination for word1 and word2 fields.
mysql> SELECT k1.word1, k2.word2 FROM `keywords` k1 LEFT JOIN `keywords` k2 ON 1=1; +-------+---------+ | word1 | word2 | +-------+---------+ | nice | job | | nice | looking | | good | job | | good | looking | +-------+---------+
And doing the following left join would give all possible combination for `word1`, `word2` and `word3` fields.
mysql> SELECT k1.word1, k2.word2, k3.word3 FROM `keywords` k1 LEFT JOIN `keywords` k2 ON 1=1 LEFT JOIN `keywords` k3 ON 1=1; +-------+---------+-------+ | word1 | word2 | word3 | +-------+---------+-------+ | nice | job | John | | nice | job | Smith | | nice | looking | John | | nice | looking | Smith | | good | job | John | | good | job | Smith | | good | looking | John | | good | looking | Smith | +-------+---------+-------+
The query could be extended by adding a rand() ordering and optional limit as well to get random combination every time a query is performed
mysql> SELECT k1.word1, k2.word2, k3.word3 FROM `keywords` k1 LEFT JOIN `keywords` k2 ON 1=1 LEFT JOIN `keywords` k3 ON 1=1 order by rand() limit 1; +-------+---------+-------+ | word1 | word2 | word3 | +-------+---------+-------+ | good | looking | Smith | +-------+---------+-------+
Do apply a concat() function to get complete phrase.
mysql> SELECT concat(k1.word1, ' ', k2.word2, ' ', k3.word3) as fullPhrase FROM `keywords` k1 LEFT JOIN `keywords` k2 ON 1=1 LEFT JOIN `keywords` k3 ON 1=1 order by rand() limit 1; +--------------------+ | fullPhrase | +--------------------+ | good looking Smith | +--------------------+
