473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL multiple table query problem - can I avoid the Cartesian?

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: ProductToCategory
- ProductID
- Category
- Descriptor
- Data

Table: ProductToSbCategory
- ProductID
- Category
- SubCategory
- Descriptor
- Data

Table: ProductToSegment
- ProductID
- Category
- SubCategory
- Segment
- Descriptor
- Data

Table: ProductToSubSegment
- 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

Feb 13 '07 #1
0 1995

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Perre Van Wilrijk | last post by:
Hello, I have 2 ways of updating data I'm using often 1) via a cursor on TABLE1 update fields in TABLE2 2) via an some of variables ... SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE...
9
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with...
7
by: Eric Slan | last post by:
Hello All: I'm having a problem that's been baffling me for a few days and I seek counsel here. I have an Access 2000 DB from which I want to run several reports. These reports are...
2
by: Tolu | last post by:
Hello I am trying to save information from one form to two tables. I have a table for Student info and Transcript line. I have a form that list all the classes (using text boxes) a student is...
3
by: Rodríguez Rodríguez, Pere | last post by:
Hello, I think I have found a query problem when the query has an alias for a table and use alias item and table name. I ilustrate the problem with a simple table and query. prr=# create...
6
by: ApexData | last post by:
I have 2 tables: Table1 and Table2. Neither one has a primary key because each table will only have 1-record. My form is a SingleForm unbound with tabs (my desire here). Using this form, in...
2
by: mmitchell_houston | last post by:
I'm working on a .NET project and I need a single query to return a result set from three related tables in Access 2003, and I'm having trouble getting the results I want. The details: ...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
2
by: Neekos | last post by:
Im working on web tool that will allow supervisors to see a list of their agents and their call stats for a call center. I have one main table that holds employee IDs and their supervisor names....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shćllîpôpď 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.