Hi, I want to write a SQL SP without using the cursor to solve a combination problem.

Here is the scenario:

5 Pool balls
Pool ball no: 1, 2, 3, 4, 5

Assign those balls to three people:
A – Choose ball 1, 2
B – Choose ball 3, 4
C – Choose ball 5

Then, let’s assume we have the following combination of the people who processed the balls:

1st combination: A + B
2nd combination: B + C
3rd combination: A + C
4th combination: A + B + C

/*====== Sample Data =========*/
combination_no Person Pool ball
1 A 1
1 A 2
1 B 3
1 B 4
2 B 3
2 B 4
2 C 5
3 A 1
3 A 2
3 C 5
4 A 1
4 A 2
4 B 3
4 B 4
4 C 5

Now, I want to know the combination of pool balls as follow:
For example:

In 1st combination: A + B will look like this:
A’s Pool ball B’s Pool Ball
Pool ball 1 Pool ball 3
Pool ball 1 Pool ball 4
Pool ball 2 Pool ball 3
Pool ball 2 Pool ball 4
…. …..
Remark: (combination 2 to3 will follow the same combination rule)

In 4th combination: A + B + C
A’s Pool ball B’s Pool Ball C’s Pool Ball
Pool ball 1 Pool ball 3 Pool ball 5
Pool ball 1 Pool ball 4 Pool ball 5
Pool ball 2 Pool ball 3 Pool ball 5
Pool ball 2 Pool ball 4 Pool ball 5


How can I get the result like below from the sample data??
/*====== Wanted Result =========*/
combination_no Person Pool ball
1 A 1
1 B 3
2 A 1
2 B 4
3 A 2
3 B 3
4 A 2
4 B 4
5 B 3
5 C 5
6 B 4
6 C 5
7 A 1
7 C 5
8 A 2
8 C 5
9 A 1
9 B 3
9 C 5
10 A 1
10 B 4
10 C 5
11 A 2
11 B 3
11 C 5
12 1 2
12 2 4
12 3 5

Homework?

Does SP in SQL SP mean Stored Procedure? You know that the ANSI standard stored procedures are called PSM?

Does you dbms product support ANSI SQL PSM?

Hi Jurl,
I mean SP = storedProcedure.
I'm using MySQL for this exercise and I suppose it support ANSI standard stored procedure. Thanks.

in theory, mysql supports ansi

in practice, you should forget about ansi and ask all your questions in the mysql forum