# Thread: Write SQL to solve a combination problem

1. Registered User
Join Date
Apr 2011
Posts
6

## Unanswered: Write SQL to solve a combination problem

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

2. Registered User
Join Date
Dec 2008
Location
At work...
Posts
92
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?

3. Registered User
Join Date
Apr 2011
Posts
6
Hi Jurl,
I mean SP = storedProcedure.
I'm using MySQL for this exercise and I suppose it support ANSI standard stored procedure. Thanks.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by oppee
I'm using MySQL for this exercise and I suppose it support ANSI standard stored procedure.
in theory, mysql supports ansi