5

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

Community
  • 1
  • 1
Nick
  • 2,877
  • 2
  • 33
  • 62
  • 1
    You could probably do it with a stored procedure. Other than that, the only way to loop is by joining with a table that has as many rows as the repetitions you need. – Barmar Nov 18 '14 at 16:26
  • Do you have a table that contains lots and lots of numbers? – Gordon Linoff Nov 18 '14 at 16:43
  • I found this: http://dba.stackexchange.com/questions/29661/sql-query-for-combinations-without-repetition which looks promising but it seems to have a limit in the number of combinations. My maximum is about potentially 100 numbers. – Nick Nov 18 '14 at 17:00
  • Although that T-SQL solution is impressive, (my)SQL may not be the right tool for this kind of problem. – Strawberry Nov 22 '14 at 12:29

1 Answers1

1

What you are trying to do is to generate the Power Set of the set of all elements with field Text == <parameter>. 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:

Finding the subsets of an array in PHP

Community
  • 1
  • 1
Bernardo Siu
  • 1,359
  • 13
  • 13