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:

Running the following left join (or SELF JOIN) would give all possible combination for word1 and word2 fields.

And doing the following left join would give all possible combination for word1, word2 and word3 fields.

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

Do apply a concat() function to get complete phrase.

Leave a Reply

Your email address will not be published. Required fields are marked *