Does anyone know how to create this QUERY? 2

[jeff5311]

if i've got table data that looks like this:

Index Option Name Option Value
763 Color Black
763 Size XL
764 Color Green
764 Size XL
765 Color White
765 Size XL

If I'm given an items' unique set of options, [Option Name]="Color" [Option Value]="Black", and [Option Name]="Size" [Option Value]="XL", what SQL statement will give me the unique [Index]???

This one's killing me... thanks!!!

 

[Steve101]

Jeff,

You're making life hard for yourself.

Change your table structure to the following:

Index Color Size
763 Blue XL
764 Green XL
765 Red XL

Then your query simply becomes:

SELECT [Index]
FROM tblYourTable
WHERE [Color] = [Enter your Color]
AND [Size] = [Enter your Size]

Hope that this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[Darrylles]

Steve,

Even without the 'global' view of the application - would not 'Size' and 'Colour' tables be more appropriate, with links to a main table?

(We have talked about this, but does this 'quick-fix' not mean that jeff will be forced to come back for more info in the future because such a quick fix encourages poor table design?)

We are here to help patrons, not increase patron visits surely ( ; - ) )

Kindest regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[jeff5311]

Thanks for your input guys!

Unfortunately, each product (ex: t-shirt) can have 1 to n options (color, size, pockets, stripes, etc...), and it's variable on a per product basis (some t-shirts have all the options, while others may have only one).

so to create a "normalized" data model, i would think it would have to be represented this way, right?

 

[Darrylles]

Hi Jeff,

As per my last post, I predicted more expectancy from you.

You have (as predicted) kicked back in with detail that should have been given from the outset.
Beginners frequently do this because of their lack of knowledge of 'business' and also db understanding.

Jeff - lets compare normalised data models (with an s you yank thicko) - you go first 'cos you've got the most money big boy. (lol)

ATB

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[jeff5311]

Darrylle,

How un-pc of me! I'll certainly use "normalised" and "colour" from now on!

As for normalisation, I'm no expert... so I'd certainly be open to modeling the data the best way possible.

I guess I'm just trying to create a compound SELECT statement to retrieve the unique [index], but I know this one won't work:

select [index] from tblOptions where
[Option Name]="size" and
[Option Value]="XL" and
[Option Name]="colour" and
[Option Value]="black"

is there another way to write this type of statement so that it only returns [index]=763 (from the data above)???

Thanks!
Jeff

 

[tlbroadbent]

Using the current table structure, you could obtain the result you want with by JOINing two (or more as needed) queries.

qrySelectSize
Select [Index], [Option Value] As ProdSize
From table
Where [Option Name]="size"
And [Option Value]=[Enter Size Needed]

qrySelectColor
Select [Index], [Option Value] As ProdColor
From table
Where [Option Name]="Color"
And [Option Value]=[Enter Desired Color]

qrySelectProduct
Select a.Index, a.ProdSize, b.ProdColor
From qrySelectSize As a
Inner Join qrySelectColor As b
On a.Index=b.Index

NOTE: I recommend not using Index or other reserved word as a column name.

Alternative query: Use Self-join of table

Select
a.Index,
ProdSize=a.[Option Value],
ProdColor=b.[Option Value]
From table As a
Inner Join table As b
On a.Index=b.Index
Where a.[Option Name]="Size"
And a.[Option Value]=[Enter Size Needed]
And b.[Option Name]="Color"
And b.[Option Value]=[Enter Desired Color] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Steve101]

Darrylle, Jeff

Good points made, but my approach does not in this instance change.

What I would do, if the number of options is finite (say up to 10), is add additional columns for each option, and modify the SQL criteria accordingly; I would'nt be adding a table for each option; I think that that might be over-kill, make for additional maintenance overhead, and possibly make the data model more complex than necessary.

If the number of options is volatile, and can change if and when different types of products are added, then one might re-think the approach, and as you've suggested review the data model.

Jeff, the model that you have is adequately normalised if the number of options is limited and non volatile. In my book, there is nothing wrong with having "sparse" fields which remain null or have a special value if they are not applicable, especially in a reference table which doesnt change a lot and has a limited number of records.

Again Darrylle, the 'quick fix' as you put it is simple, and may just be the best. I have taught myself over the years to use what I call 'pre-emptive design' to try to accomodate what might occur in the future (eg. changing business rules, etc.). You're obviously applying this methodology, and that is good. I've also however learned to 'scope' solutions and not over-complicate them if it can be avoided, even if it means breaking the rules occassionally (which I had drummed into me via a formal computer science education some time ago, and which I respect enormously). Casting the net too wide and over-engineering a solution can cause problems of its own; believe me, I've done it.

Anyway, always great to exchange views,

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[jeff5311]

Terry,

Wow! Using your self-join SQL I created this:

SELECT a.Index,
a.[Option Value] AS Expr1,
b.[Option Value] AS Expr2
FROM tblProductOptions
AS a INNER JOIN tblProductOptions
AS b ON a.Index = b.Index
WHERE
(((a.[Option Name])="Size&quot AND
((a.[Option Value])=[Enter Size Needed]) AND
((b.[Option Name])="Color&quot AND
((b.[Option Value])=[Enter Desired Color]));

which seems to work... unfortunately, I'm completely unsure of what it's all doing!!!

i assume that as i add more options (pockets, stripes, etc...), i can just add more expressions. like this:

SELECT a.Index,
a.[Option Value] AS Expr1,
b.[Option Value] AS Expr2,
c.[Option Value] AS Expr3,
FROM tblProductOptions
AS a INNER JOIN tblProductOptions
AS b ON a.Index = b.Index
(NOT SURE WHAT TO ADD HERE)
WHERE
(((a.[Option Name])="Size&quot AND
((a.[Option Value])=[Enter Size Needed]) AND
((b.[Option Name])="Color&quot AND
((b.[Option Value])=[Enter Desired Color])
((c.[Option Name])="Stripes&quot AND
((c.[Option Value])=[Enter Vertical Or Horizontal]));

will this work???

whatever you're getting paid, it's certainly not enough!

thanks again,
Jeff

 

[Steve101]

Jeff,
I concur. Terry's approach has that mixture of (a) Simplicity and (b) flexibility (ie. data driven). It will allow easy addition of new options without the need to change any data entry forms or table definitions. It does however need a number of supporting queries added/changed to support new options/combinations. This could also probably be handled generically through code, with a bit of additional thought. Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[tlbroadbent]

will this work???

Yes it will.

SELECT a.Index,
a.[Option Value] AS Expr1,
b.[Option Value] AS Expr2,
c.[Option Value] AS Expr3,
FROM tblProductOptions AS a
INNER JOIN tblProductOptions AS b
ON a.Index = b.Index
(NOT SURE WHAT TO ADD HERE)
INNER JOIN tblProductOptions AS c
ON a.Index = c.Index

WHERE
(((a.[Option Name])="Size&quot AND
((a.[Option Value])=[Enter Size Needed]) AND
((b.[Option Name])="Color&quot AND
((b.[Option Value])=[Enter Desired Color])
((c.[Option Name])="Stripes&quot AND
((c.[Option Value])=[Enter Vertical Or Horizontal])); Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[jeff5311]

Steve - you're right, your solution would work also, but would require me to know all options and corresponding values BEFORE coding (though you didn't know, this is a luxury I don't, and will never have!). Regardless, thanks for your input.

Terry - Thanks for the elegant self-join solution! Definitely an "expert" post. Now I just have to determine the optimal way to dynamically generate this SQL in my code. I'll try this tomorrow... it's getting late for me!

Thanks again everyone!!!
Jeff

 

[tlbroadbent]

Here is another solution that you may prefer in Access. I don't often think of using Crosstab queries because they aren't available in SQL Server or the SQL* language. Access does provide the capability and as long as you use Access this may be the best method for this requirement.

Create and save a crosstab query. You can use the wizard to help design the query. The SQL will look like the following.

qry_ProductCrosstab
TRANSFORM First([ProdTable].[OptionValue]) AS FirstOfOptionValue
SELECT [ProdTable].[ProdIndex]
FROM ProdTable
GROUP BY [ProdTable].[ProdIndex]
PIVOT [ProdTable].[OptionName];

Then create a selection query that uses the crosstab query as source.

qry_SelectProduct
PARAMETERS [Enter Color] Text ( 255 ),
[Enter Size] Text ( 255 ),
[Enter Stripes] Text ( 255 );
SELECT c.ProdIndex, c.Color, c.Size, c.Stripes
FROM qry_Product_Crosstab AS c
WHERE (((c.Color)=[Enter Color])
AND ((c.Size)=[Enter Size])
AND ((c.Stripes)=[Enter Stripes]));

Once you create the Crosstab query, it will automatically handle all options. You will only need to modify the Select query for each new option added.

*SQL can simulate Crosstab queries but the code is rather complex. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[jeff5311]

Thanks again Terry! The crosstab solution makes sense, but I like your self-join solution better. In case anyone is wondering, here's the self-join with multiple option values:

SELECT a.[Index], a.[Option Value], 
       b.[Option Value], 
       c.[Option Value], 
       d.[Option Value]

FROM ((tblProductOptions AS a 
INNER JOIN tblProductOptions AS b ON a.Index = b.Index) 
INNER JOIN tblProductOptions AS c ON a.Index = c.Index) 
INNER JOIN tblProductOptions AS d ON a.Index = d.Index

WHERE (
((a.[Option Name])="Size") AND 
((a.[Option Value])=[Enter Size Needed]) AND 
((b.[Option Name])="Color") AND 
((b.[Option Value])=[Enter Desired Color]) AND 
((c.[Option Name])="Stripes") AND 
((c.[Option Value])=[Enter Stripes]) AND 
((d.[Option Name])="Pockets") AND
((d.[Option Value])=[Enter Pockets])
);

Terry, again... you're a lifesaver!

Thanks All!
Jeff