Hello Everyone,
I have been asked to try and create a single SQL query to retrieve
product information from a database. The way that data is arranged is
that in some tables there are user defined "attributes " or
"dimensions " that in turn connect to the actual product table via a
many-many (using a linking table). In each linking table there is a
combination of the "dimension" , the productID, and the "fact" that is
stored against the dimension for that product.
There are 5 (five) of these "dimension" type tables storing product /
dimension information, as well as of course the product table itself.
The only thing that can be used as a unique key through the entire
query is the ProductID / Category.
The actual information about the product is defined via the
relationships between tables in the db structure, also resulting in
compound keys the deeper into the structure you go. The
ClusterProducts table below is used for "grouping" products together,
and the other tables allow the definition and storage of "facts" that
are required to define a product at each level of the structure.
It is possible that there are a range of possible facts to be filled
out by the user in defining their products, but they may not fill out
all the facts for all the products. So that in a given table we may
have product / descriptor / fact information for some of the possible
combinations but not all.
The problem comes down to this:
Is there a way of retrieving a result set that contains the product
information collected from all tables in a single SQL statement? I
give some table examples below...
Table: Product
- ProductID
- Category
- SubCategory
- Segment
- SubSegment
- Manufacturer
- Brand
Table: ClusterProducts
- ProductID
- Category
- Dimension
- Cluster
Table: ProductToCatego ry
- ProductID
- Category
- Descriptor
- Data
Table: ProductToSbCate gory
- ProductID
- Category
- SubCategory
- Descriptor
- Data
Table: ProductToSegmen t
- ProductID
- Category
- SubCategory
- Segment
- Descriptor
- Data
Table: ProductToSubSeg ment
- ProductID
- Category
- SubCategory
- Segment
- SubSegment
- Descriptor
- Data
I can produce the necessary result set with some vba and a few
recordsets, however that keeps the ability to use the resultset
limited to a single application. If it were possible to do this in a
single SQL statement then it could be used by many apps as it could be
stored in the DB as a query.
Any help with this would be greatly appreciated. Maybe I just missed
something really obvious, I'm having one of those weeks....
Cheers and Thanks in Advance
The Frog
I have been asked to try and create a single SQL query to retrieve
product information from a database. The way that data is arranged is
that in some tables there are user defined "attributes " or
"dimensions " that in turn connect to the actual product table via a
many-many (using a linking table). In each linking table there is a
combination of the "dimension" , the productID, and the "fact" that is
stored against the dimension for that product.
There are 5 (five) of these "dimension" type tables storing product /
dimension information, as well as of course the product table itself.
The only thing that can be used as a unique key through the entire
query is the ProductID / Category.
The actual information about the product is defined via the
relationships between tables in the db structure, also resulting in
compound keys the deeper into the structure you go. The
ClusterProducts table below is used for "grouping" products together,
and the other tables allow the definition and storage of "facts" that
are required to define a product at each level of the structure.
It is possible that there are a range of possible facts to be filled
out by the user in defining their products, but they may not fill out
all the facts for all the products. So that in a given table we may
have product / descriptor / fact information for some of the possible
combinations but not all.
The problem comes down to this:
Is there a way of retrieving a result set that contains the product
information collected from all tables in a single SQL statement? I
give some table examples below...
Table: Product
- ProductID
- Category
- SubCategory
- Segment
- SubSegment
- Manufacturer
- Brand
Table: ClusterProducts
- ProductID
- Category
- Dimension
- Cluster
Table: ProductToCatego ry
- ProductID
- Category
- Descriptor
- Data
Table: ProductToSbCate gory
- ProductID
- Category
- SubCategory
- Descriptor
- Data
Table: ProductToSegmen t
- ProductID
- Category
- SubCategory
- Segment
- Descriptor
- Data
Table: ProductToSubSeg ment
- ProductID
- Category
- SubCategory
- Segment
- SubSegment
- Descriptor
- Data
I can produce the necessary result set with some vba and a few
recordsets, however that keeps the ability to use the resultset
limited to a single application. If it were possible to do this in a
single SQL statement then it could be used by many apps as it could be
stored in the DB as a query.
Any help with this would be greatly appreciated. Maybe I just missed
something really obvious, I'm having one of those weeks....
Cheers and Thanks in Advance
The Frog