MS Access SQL Product Combination Export

Question

So I'm a bit rusty in SQL and could really need some help here - pretty please.

I have an online shop system based on MS Access in my hands and I need to export all possible product variants.

I have a table "VariantTypes" which contains all needed information. It looks like this:

ProductFKID | VariantTypeFKID | VariantValueFKID | Surcharge

The data for one product looks like this:

2 | 3 | 12 | 0,00 €

2 | 3 | 13 | 39,00 €

2 | 3 | 14 | 39,00 €

2 | 134 | 556 | 0,00 €

2 | 134 | 557 | 90,00 €

2 | 134 | 558 | 90,00 €

Now for the new import I need every possible variant combination, which should look like this:

2 | 3 | 12 | 0,00 € | 134 | 556 | 0,00 €

2 | 3 | 12 | 0,00 € | 134 | 557 | 90,00 €

2 | 3 | 12 | 0,00 € | 134 | 558 | 90,00 €

2 | 3 | 13 | 39,00 € | 134 | 556 | 0,00 €

2 | 3 | 13 | 39,00 € | 134 | 557 | 90,00 €

2 | 3 | 13 | 39,00 € | 134 | 558 | 90,00 €

2 | 3 | 14 | 39,00 € | 134 | 556 | 0,00 €

2 | 3 | 14 | 39,00 € | 134 | 557 | 90,00 €

2 | 3 | 14 | 39,00 € | 134 | 558 | 90,00 €

And of course, if I have 3 variant types for one product, it should work the same way. The best example for this is:

A product with the colors BLACK, WHITE, RED and sizes S,M,L and I need:

I'm sure there is an easy way to solve this, 'cause I learned it quite some time ago in school. I just can't remember how.

Thanks for your help in advance!

Greets, KaptainEaglu

UPDATE:

I managed to create following statement to create the cartesian product:

SELECT v1.ProductID, v1.Name, v1.Value, v1.Surcharge, v2.Name, v2.Value, v2.Surcharge

FROM [smartstore].[dbo].[smarstorevariants] v1

CROSS JOIN [smartstore].[dbo].[smarstorevariants] v2

WHERE v1.ProductID = v2.ProductID AND v1.Name != v2.Name

ORDER BY v1.ProductID desc

But as a result I don't get 9 combinations as result but 18. E.g.: Black-S, Black-M,... and S-Black, S-White,...

I know, technically they are different combinations but for my shop-export they are double combinations.

Any ideas on how to solve this?

You are asking for a *Cartesian Product*; implemented with CROSS JOIN operator and no ON clause. — Pieter Geerkens, May 09 '15 at 15:02
First Google search on "ms-access sql cross join": http://stackoverflow.com/questions/1231676/how-to-use-cross-join-in-access — Pieter Geerkens, May 09 '15 at 15:58
i just created an odbc-export from ms-access and created an MS-SQL Server Table. — KaptainEaglu, May 09 '15 at 16:30
When SQL Server is available it will always be a better choice for the data storage solution than MS-Access, even though the front end might remain in MS-Access. — Pieter Geerkens, May 09 '15 at 16:35
I worked out the CROSS JOIN, but the problem with the cartesian product is, that I get the combinations twice. with following SQL statement, for the above example,I don't get 9 rows but 18... Black-S, Black-M, and so on. and then vice versa S-Black, S-White, S-Red... What can I do? SELECT v1.ProductID, v1.Name, v1.Value, v1.Surcharge, v2.Name, v2.Value, v2.Surcharge FROM [smartstore].[dbo].[smarstorevariants] v1 CROSS JOIN [smartstore].[dbo].[smarstorevariants] v2 WHERE v1.ProductID = v2.ProductID AND v1.ProductID = '2000.w004' AND v1.Name != v2.Name ORDER BY v1.ProductID desc — KaptainEaglu, May 09 '15 at 17:44
Add the update to your question where can be formatted to be readable. — Pieter Geerkens, May 09 '15 at 18:06

score 0 · Answer 1 · answered May 10 '15 at 07:02

It's easier in Access SQL:

ProductFKID | VariantTypeFKID | VariantValueFKID | Surcharge

SELECT DISTINCT
    v1.ProductFKID, 
    v1.VariantTypeFKID, v1.VariantValueFKID, v1.Surcharge,
    v2.VariantTypeFKID, v2.VariantValueFKID, v2.Surcharge    
FROM 
    VariantTypes As v1,
    VariantTypes As v2
WHERE 
    v1.ProductFKID = v2.ProductFKID  
    AND 
    v1.VariantTypeFKID <> v2.VariantTypeFKID 
ORDER BY 
    v1.ProductFKID Desc

MS Access SQL Product Combination Export

1 Answers1