MYSQL - Return Product Option Variants

Question

I'm in the process of moving product option data into new more efficient tables.

I've created the new tables:

Options:  ID  / Product_ID  / Option_Text

Values:   ID  / Options_ID  / Value_text

And I've put all the legacy options into these tables. My HUGE hurdle is creating the data for the variant tables:

Variants: ID  / Product_ID  / Price / SKU

Variant_Values: ID / Options_ID / Variants_ID

This is easy to do in Javascript using Arrays and Cartesian for the NEW products. But I'm looking to use MYSQL to move the data over. It doesn't have to be efficient as I'm going to run this script once.

So lets say I have the following data:

Options:

36  |  100  |  Color
37  |  100  |  Size
38  |  100  |  Logo

Values:

72  |  36  |  Blue
73  |  36  |  Red
74  |  37  |  Large
75  |  37  |  Med
76  |  37  |  Small
77  |  38  |  Shark
78  |  38  |  Unicorn

So in the new variant values table I'll need to combine all the options like:

BLUE - LARGE - SHARK
BLUE - LARGE - UNICORN
BLUE - MED - SHARK

But to put them in the varient values table I really only need to group them together like:

72 | 1
74 | 1
77 | 1
72 | 2
74 | 2
78 | 2
72 | 3
75 | 3
77 | 3

So is there a query that I can run that will give me that final table?

if I run something like:

SELECT (v.id), v.`Value_text`, o.`Option_Text`
    FROM `Values` AS v
    JOIN `Options` AS o ON o.`id` = v.`Options_ID ` AND o.`Product_ID` = 162961
GROUP BY o.`id`;

I get something like:

"72"    "Blue"          "Color"
"74"    "Large"         "Size"
"77"    "Shark"         "Logo"

But I don't know how to create a query to give me ALL the combinations..

Answer 1

you need to :

Products: ID   /  ProductName

Options:  ID  / Option_Text

Values:   ID  / Options_ID  / Value_text

Product_option: ProductID / OptionID / ValueID