0

I have a single table "Data" in my MySQL database. This contains the following structure:

CID | CVALUE
------------------
1   | 1A
1   | 1B
1   | 1C
2   | 2A
2   | 2B
3   | 3A
4   | 4A
4   | 4B
4   | 4C
5   | 5A
5   | 5B

I need a cartesian product that contains all the combinations of CVALUE column. Hence I use the below query which refers to the same table with multiple aliases to treat them as different tables.

select a.cvalue, b.cvalue, c.cvalue, d.cvalue, e.cvalue
from data a, data b, data c, data d, data e
where a.cid = 1 and b.cid = 2 and c.cid = 3 and d.cid = 4 and e.cid = 5;

The above query works, and I am able to get a result set as below

cvalue | cvalue | cvalue | cvalue | cvalue
--------------------------------------------
1A     | 2A     | 3A     | 4A     | 5A
1A     | 2A     | 3A     | 4A     | 5B
1A     | 2A     | 3A     | 4B     | 5A
1A     | 2A     | 3A     | 4B     | 5B
1A     | 2A     | 3A     | 4C     | 5A
1A     | 2A     | 3A     | 4C     | 5B
.... and so on for the remaining combinations.

The problem with this approach is that I am not sure how many unique CID values I will have, and it gets cumbersome to add the column entries manually in the query, as well as add alias to the same table.

I was wondering if there is any query or function that can be used in MYSQL to generate such a cartesian product of a single table. Any help or pointers in this regard will be appreciated.

Kim Smith
  • 105
  • 8
  • MySQL has no support for hierarchical or recursive queries. You can do this, but it requires dynamic SQL . . . or perhaps a looping structure with temporary tables. – Gordon Linoff Jul 20 '16 at 12:27
  • I think it's possible with a very ugly construct of `SET`, `FOR` and `COUNT` with some subselects. But it's too much logic for a database, tho. – Daniel W. Jul 20 '16 at 12:41

0 Answers0