Newbie SQL Question (Join?)

[jeff5311]

Can any of you SQL gods help me?

I have two tables (tblProducts, tblProductOptions)

tblProducts has these fields:
Index
Product Code
Base Price

tblProductOptions has these fields:
Index
Option Name
Option Value
Upcharge

I have a relationship set up between the 2 tables such that any one product can have multiple product options (the joined field is Index).

How can I find write a SELECT statement to get a product's unique index if I have the [Product Code] and its set of [Option Name](s), [Option Value](s)?

Thanks so much for your help!!!

Jeff

 

[scriverb]

I hope I understood your request here. You said that you wanted to retrieve the Index value from the tblProducts where you have the Product Code and the Option Names and Option Values from the table tblProductOptions.

First of all since the Product Code is a unique item you don't need to even use the tblProductOptions information for this one. Simply use the code below to select the Index value.

Select tblProducts.Index, tblProducts.[Product Code]
From tblProducts
Where tblProducts.[Product Code] = 'xxxxx';

If there is more to this request please get back to me and explain a little further.

Bob Scriver

 

[jeff5311]

Bob,

Thanks for the response! Sorry for being a bit cryptic...

I have the [Product Code] and its corresponding set of multiple [Option Name](s) and [Option Value](s), and I'm trying to create a SELECT statement which retrieve the unique [Index]

For instance, a product (let's say a t-shirt) has a [Product Code] = "t-shirt"

and list of options:
[Option Name] = "Color"
[Option Value] = "White"
[Option Name] = "Size"
[Option Value] = "XL"
[Option Name] = "Front Pocket"
[Option Value] = "Yes"
etc...

I want to create an SQL statement using it's [Prode Code] and corresponding options to get the unique [Index]

Does any of this make sense?

Thanks so much for your help!!!

 

[scriverb]

Jeff:
I still think that all you need to use is the following:

Select tblProducts.Index, tblProducts.[Product Code]
From tblProducts
WHERE (((tblProducts)=[Enter Product Code: ]));

You see the Product Code T-Shirt will locate the only record in the tblProducts and the INDEX field is then available. You do not need to even look at the secondary (many) table to retrieve the index. Yes, it is in each of those corresponding records but it is not necessary to make that connection to retrieve its value. It is available in the tblProducts.

I hope I understanding your needs here. The above query prompts you for the Product Code. Just type in T-shirt and the query will return two columns from tblProducts: Index and Product Code.

Get back with me again if I am missing something here.

Bob Scriver

 

[jeff5311]

Bob,

Thanks again!!!

I think I forgot one important detail. [Product Code] is not unique in the table tblProducts (only [Index] is).

Taking the above example, I have 10 products all with the [Product Code] = "t-shirt". The only thing that differentiates them from one another is their set of [Opion Name] and [Options Values].

The data would look like this:

tblProducts
[Index] [Product Code] [Base Price]
1 t-shirt $10.00
2 t-shirt $10.00
3 t-shirt $10.00
4 pants $24.00
5 pants $24.00
etc...


tblProductOptions
[Index] [Option Name] [Option Value] [Upcharge]
1 Size XL $5.00
1 Color Black $0.00
1 Pocket Yes $2.00
2 Size L $0.00
2 Color Black $0.00
2 Pocket Yes $0.00
etc...

as you can see from the above data, the only difference between the product 1 and product 2 is its associated options. If I were to SELECT just the [Product Code] it would return ALL the t-shirts listed. So... what I really need is to the unique [Index] give the [Product Code] and all its options.

It's like some CRAZY kind of reverse join! But I'm sure there's something simple I can do to get it to work.

THANK SO MUCH!!! THIS ONE'S KILLING ME!!!

Jeff

 

[scriverb]

Okay, now I get it. Try this.

SELECT tblProducts.INDEX, tblProducts.[Product Name], tblProductOptions.INDEX, tblProductOptions.[Option Name], tblProductOptions.[Option Value], tblProductOptions.UpCharge
FROM tblProducts INNER JOIN tblProductOptions ON tblProducts.INDEX = tblProductOptions.INDEX
WHERE (((tblProducts.[Product Name])=[Enter Product Name: ]) AND ((tblProductOptions.[Option Name])=[Enter Option Name: ]) AND ((tblProductOptions.[Option Value])=[Enter Option Value: ]));

Give this one a try. I created the tables as you described but the proof is do they do what you want. Check them out and get back to me.

Bob Scriver

 

[jeff5311]

Right!

But... that SQL only allows for 1 set of options, resulting in multiple products instead of just 1. It's almost like I need to be able to iterate the option part of the SQL statement (in the where clause), like:

SELECT * FROM tblProducts INNER JOIN tblProductOptions ON tblProducts.INDEX = tblProductOptions.INDEX
WHERE (((tblProducts.[Product Name])="t-shirt&quot
AND ((tblProductOptions.[Option Name])="Size&quot
AND ((tblProductOptions.[Option Value])="XL&quot
AND ((tblProductOptions.[Option Name])="Color&quot
AND ((tblProductOptions.[Option Value])="Black&quot
AND ((tblProductOptions.[Option Name])="Pocket&quot
AND ((tblProductOptions.[Option Value])="Yes&quot);

but SQL won't allow me to do this...

Will it require some sort of subselect???

Thanks! I wish i knew SQL as well as you!

Jeff