Results 1 to 4 of 4
  1. #1
    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. #2
    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. #3
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oppee View Post
    I'm using MySQL for this exercise and I suppose it support ANSI standard stored procedure.
    in theory, mysql supports ansi

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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •