I have a strange request in mySQL. I found many ways to do this for pairs of combinations or a certain other number by adding more joins, but I am wondering if there is a dynamic way of doing it for any number of combinations.
To explain if I have a table table has 1 column (column_id) and (column_text)
Id | Text
--------
1 | A
2 | B
3 | B
4 | B
5 | A
Then by running a procedure GetCombinations with parameter A should yield:
CombinationId | Combinations
---------------------------
1 | 1
2 | 5
3 | 1,5
by running a procedure GetCombinations with parameter B should yield:
CombinationId | Combinations
---------------------------
1 | 2
2 | 3
3 | 4
4 | 2,3
5 | 2,4
6 | 3,4
7 | 2,3,4
Obviously the larger the number, then I expect an exponential increase of results.
Is such a query even possible? All I could find was results using Joins limiting the length of each result to the number of Joins.
Thank you
UPDATE
I have found an article here but the maximum number of combinations should be small (max 20 or so). In my case with a 100 combinations I calculated that it would produce: 9426890448883247745626185743057242473809693764078951663494238777294707070023223798882976159207729119823605850588608460429412647567360000000000000000000099 rows (lol)
So I will classify my answer as infeasible
However is there a way to get this result with max 2 combinations?
CombinationId | Combinations
---------------------------
1 | 2
2 | 3
3 | 4
4 | 2,3
5 | 2,4
6 | 3,4
I have found a query to get all combinations using JOIN but I am not sure how to produce the combination id and also how to get the individual rows.
UPDATE 2
Solved it using
SELECT @rownum := @rownum + 1 AS 'CombinationId'
cross join (select @rownum := 0) r
And I did the query with UNION ALL
解决方案
What you are trying to do is to generate the Power Set of the set of all elements with field Text == . As you already found out, this number grows exponentially with the length of the input array.
If you can solve it in other language (say, php), take a look at this: