Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-15 : 01:58:26
|
Brian writes "First of all, love your site. I have found a lot of useful information here.
Here's my question:
Suppose I have the following tables:
tblAttibutes
Code Name
----- ----
C Color
S Size
tblAttributeVariations
AttributeCode code Name
------------- ---- ----
C 01 blue
C 02 red
S 01 small
S 02 medium
tblShoppingCart
SKU Qty
------------- ---
123456_C01S01 1
123456_C01S02 2
123456_C02S01 1
What I would like is a query that returns the attribute name and variation name so that it can be displayed as:
qty sku attribute1 attribute2
1 123456_C01S01 color:blue size:small
2 123456_C01S02 color:blue size:medium
1 123456_C02S01 color:red size:small
Using these rules:
The attributes will always be after the "_" in the sku.
The attributecode will always be a single letter
The variationcode will always be a number, but not necessary 2 digits.
SKUs may contain any number of attribute/variation combination -
some contain only color(one size fits all),
some contain size,color,gender (mens large red shirt)
I currently do this programatically by retrieving the sku and attributes independently. How could I perform this with one query?
I am able to alter the way the tables are currently set up if that would help.
Thanks for your time.
Brian" |
|
|
|
|
|
Joining to multiple rows of the same table