Need Help

athanatos · September 8th, 2004, 10:05 PM

Hi

I am using asp and i am trying to do a search on 2 feature id's that a single product can have. What i need to do is to return the products that are between a Weight range of x to y and a height range of a to b meters.

These are the 2 statements that work correctly on their own but i cant get my head around to combining them at the moment for it to be able to filter out the correct products.

So for example if my Height is between 6 - 9 at 7.0 meters and my Weight is between 2000 and 2999 at 2800 how do i combine these sql statements to get what i need done.

SELECT a.ProductID, a.LongName, b.ProductID
FROM e2Shop_tbl_Products a INNER JOIN
e2Shop_tbl_ProductFeatureValues b ON a.ProductID = b.ProductID
WHERE b.FeatureID = 14 AND ValueNumber >= " & HeightA & " AND ValueNumber <= " & HeightB & "

SELECT a.ProductID, a.LongName, b.ProductID
FROM e2Shop_tbl_Products a INNER JOIN
e2Shop_tbl_ProductFeatureValues b ON a.ProductID = b.ProductID
WHERE b.FeatureID = 13 AND ValueNumber >= " & WeightA & " AND ValueNumber <= " & WeightB & "

Thanks if anyone can help me.

sinapra · September 8th, 2004, 11:12 PM

hi,
well, can you use a single query and use a perfect join, and for the different ranges, you can specify 'between', I mean selext * from table where a.id=b.id and a.height between 'specify the range here'.
I hope you understood what I meant.
regards

sinapra

athanatos · September 9th, 2004, 01:28 AM

Yeah i understand what you are telling me but i need to compare the height and width against the product at the same time.

ProductID = 1 Which has FeatureID = 13 ValueNumber = 6.35
ProductID = 1 Which has FeatureID = 14 ValueNumber = 2800

i need to compare both and use both at the same time to filter it out, if i do 1 select can you perform the second select once the results have been returned?

sinapra · September 9th, 2004, 02:08 AM

See, let me explain it, you mean to say, for productId 1, you want to see two featureIds. Say you have 4 checkboxes and a person has selected 2 checkboxes that is featureId1 and featureId2. Now you can use a for loop where you can use each featureId one by one and display the result appropriately.
I hope you got my point here,
regards

sinapra

happygv · September 9th, 2004, 06:01 AM

Hi Athanatos,

If I understand that right, Is it that you are trying to search for a product with a featureId that corresponds to its weight between some range and the same product with another featureid, that corresponds to its height between some range?

Then you may try this.

Code:

SELECT a.ProductID, a.LongName, b.ProductID 
FROM e2Shop_tbl_Products a INNER JOIN 
e2Shop_tbl_ProductFeatureValues b ON a.ProductID = b.ProductID WHERE 
    b.FeatureID = 13 AND ValueNumber BETWEEN " & WeightA & " AND " & WeightB & " AND " 
    b.FeatureID = 14 AND ValueNumber BETWEEN " & HeightA & " AND " & HeightB

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

athanatos · September 9th, 2004, 07:33 PM

Yeah This is what i thought originally aswell, but when i do this i get a return of 0 no products .. but i have about 40 that match in some form or another .. is there something there that i am doing wrong for it to return a zero result ..

Thanks again for your help.

happygv · September 10th, 2004, 05:24 AM

How do you say there are 40 such rows that match this, when you could see zero result using this query?

In that case for a productID, FEATUREID 13 and 14 shouldn't exisit.

Can you post your table structure and sample rows of a complete result set, that you are looking for to understand it better and explain on what you would be missing...

Cheers!

_________________________
- Vijay G
Strive for Perfection