A few notes on joining a MySQL table to itself to get unique combination of field data

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 |
+--------------------+

Leave a Reply