SELECT Query, Driving Me Nuts!!

[mystifier]

I have three tables:



Product with fields ProductID, ProductName (eg. 100, Shirt)
Option with fields ProductID, OptionID, OptionName (eg. 100, 200, Colour)
OptionValue with ProductID, OptionID, OptionValueID, ValueName (eg. 100, 200, 300, Blue)

They are related by same named ID fields (I realise that ProductID should be redundant in OptionValue but it is there if it helps).

Products can have any number of Options; Options can have any number of OptionValues.

I need a Query (or Procedure if no nested select is possible) to list every possible unique stock item.

Example:
Shirt, Large, Green, Short-Sleeved, with-Logo
Shirt, Large, Green, Short-Sleeved, no-Logo
Shirt, Large, Green, Long-Sleeved, with-Logo
Shirt, Large, Green, Long-Sleeved, no-Logo
Shirt, Large, Blue, Short-Sleeved, with-Logo
Shirt, Large, Blue, Short-Sleeved, no-Logo
Shirt, Large, Blue, Long-Sleeved, with-Logo
Shirt, Large, Blue, Long-Sleeved, no-Logo
etc., etc.

Please note, this is NOT:

SELECT Product.ProductName, Option.OptionName, OptionValue.ValueName
FROM product
INNER JOIN Option ON Option.ProductID = Product.ProductID
INNER JOIN OptionValue ON OptionValue.OptionID = Option.OptionID

Which would give, for Example:
Shirt, Colour, Green
Shirt, Colour, Blue
Shirt, Size, Large
Shirt, Size, Medium
Shirt, Size, Small
etc. etc.

This is driving me nuts!!!

[pbaldy]

I'm not sure I understand, but does this get you what you want?

SELECT Product.ProductName, Option.OptionName, OptionValue.ValueName
FROM product, Option, OptionValue

That would result in the Cartesian product, or every possible combination.

[mystifier]

Thanks pbaldy, but there has to be a bit more to it because this would give me every product option and value for every single product not just those that relate, which would be enormous.

Also, it would produce a Product Option Value result such as:

Shirt, Size, Small
Shirt, Size, Medium
Shirt, Size, Large
Shirt, Colour, Blue
....
Shirt, Cup-size, 34D

I am still no further forward myself

[pbaldy]

Can you post a sample db or something? I'm not visualizing the data.

[mystifier]

Hi pbaldy,

I am attempting to provide a minimal problem; the actual database is much more complicated and would probably cloud things.

Three tables are basically:

PRODUCT
---------
ProductID (#link1#)
ProductName

OPTION
--------
ProductID (#link1#)
OptionID (#link2#)
OptionName

OPTIONVALUE
-------------
ProductID (available #link1# if required)
OptionID (#link2#)
ValueName

PRODUCTS are clothes (eg. Shirt, Trousers, Jumper)
OPTIONS are attributes (eg. Colour, Size, Fabric)
OPTIONVALUES are values (eg. Red, Small, Cotton)

Different Products each have different Options, each of which have different values.

I need to be able to generate every possible unique item (eg.
Shirt, Large, Green, Cotton
Trousers, Long, Grey, With-Belt
Cap, Size7
etc.
)
so that I can apply Stock Control.