How to build a metric table?

Discussion in 'MySQL' started by grutland, Mar 15, 2011.

0
  1. #1
    Hi,

    I want to be able to build a matrix table with every possible selection of some values.
    For example, if I have the following sets of values:

    Set 1: set1_value1, set1_value2, set1_value3
    Set 2: set2_value1, set2_value2
    Set 3: set3_value1, set3_value2, set3_value3, set3_value4

    I want to be able to create a table so that it has the data stored like this, each set is to become a column in this table:
    Row 1: set1_value1
    Row 2: set1_value1, set2_value1
    Row 3: set1_value1, set2_value2, set3_value1
    ...
    Row #: set3_value1
    Row #: set3_value1, set2_value1

    My first approach was a cross join, but that only did it so that there was always something selected from every set like this:
    Row 1: set1_value1, set2_value1, set3_value1
    Row 2: set1_value1, set2_value1, set3_value2
    Row 3: set1_value1, set2_value1, set3_value3
    Row 5: set1_value1, set2_value1, set3_value4
    ...

    Any one know what I can do to get the desired result?

    Cheers.
     
    grutland, Mar 15, 2011 IP
  2. grutland

    grutland Active Member

    Messages:
    86
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    71
    #2
    Any one?

    I realise I put "metric" in the post title, it was meant to be matrix.
    I've been looking into crosstab tables but still can't work out a solution.

    I just need to build a table with every possible combination, including null values.
     
    grutland, Mar 16, 2011 IP
  3. jkl6

    jkl6 Peon

    Messages:
    70
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Cross join doesn't give you the NULL combination, what you need is a FULL OUTER JOIN. Unfortunately, MySql doesn't support this type of join, so you'll have to simulate it by using LEFT JOIN or RIGHT JOIN and UNION. If you can install Oracle, you can try the FULL OUTER JOIN.
     
    jkl6, Mar 16, 2011 IP