SQL Giving where clause dominance?

[Dxmxgxd]

Hi Guys;
I am having some trouble with a category filter I am making for VM2.0; the issue is. When I run the following query;

"SELECT * FROM jos_virtuemart_products INNER JOIN jos_virtuemart_product_categories ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_categories.virtuemart_product_id INNER JOIN jos_virtuemart_product_customfields ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_customfields.virtuemart_product_id WHERE virtuemart_category_id = '27' AND custom_value BETWEEN '0' AND '.3' OR custom_value BETWEEN '.3' AND '.6' OR custom_value BETWEEN '.6' AND '.9'"

The results are not refined by the category ID, it is still resulting in products form other categories.

My first assumption is that it has something to do with the succeeding clauses. (category_id = 27 AND custom_value BETWEEN 0 AND 3 OR...).

Is there any way to give the virtuemart_category_id = '27' clause dominance over the others? Or could I run a query preceding the other clauses so that they are only entries from the category of 27? Like creating a temporary table including all products from category with id of 27 and filter down from here?

This is fairly urgent; any assistance will be much appreciated.

[Old Pedant]

SELECT * 
FROM jos_virtuemart_products INNER JOIN jos_virtuemart_product_categories 
ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_categories.virtuemart_product_id 
INNER JOIN jos_virtuemart_product_customfields 
ON jos_virtuemart_products.virtuemart_product_id=jos_virtuemart_product_customfields.virtuemart_product_id 
WHERE virtuemart_category_id = '27' 
AND custom_value BETWEEN '0' AND '.3' 
OR custom_value BETWEEN '.3' AND '.6' 
OR custom_value BETWEEN '.6' AND '.9'

Harken back to your 5th or 6th grade math class.

What is the value of

     3 + 4 * 5

???

If you answered 35, go back to school.

Multiplication has higher priority than addition, so the correct answer is 23. You multiply first, then add.

If you wanted the 35 answers, you would have written

   ( 3 + 4 ) * 5

Same thing with AND and OR operators, in all computer languages.

AND has a higher precedence than OR.

So when you do

WHERE virtuemart_category_id = '27' 
AND custom_value BETWEEN '0' AND '.3' 
OR custom_value BETWEEN '.3' AND '.6' 
OR custom_value BETWEEN '.6' AND '.9'

you are *REALLY* writing

WHERE ( virtuemart_category_id = '27' AND custom_value BETWEEN '0' AND '.3' )
OR custom_value BETWEEN '.3' AND '.6' 
OR custom_value BETWEEN '.6' AND '.9'

so if custom_value matches either of those LAST TWO conditions, the first AND condition is ignored.

Now try

WHERE virtuemart_category_id = '27' 
AND ( custom_value BETWEEN '0' AND '.3' 
      OR custom_value BETWEEN '.3' AND '.6' 
      OR custom_value BETWEEN '.6' AND '.9' )

Not to ask a dumb question, but what is the point of the three BETWEEN tests???

BETWEEN is *inclusive* so you could have accomplished the same thing with

WHERE virtuemart_category_id = '27' 
AND custom_value BETWEEN '0' AND '.9'

and never had to worry about the OR conditions.

[Dxmxgxd]

Thank you very much; I hadn't realized that the addition of brackets within a query would isolate them from the other clauses.

The reason I have used three where clauses for the custom_value is because users can select any of four ranges (0-.3, .3-.6, .6-.9, .9<).

And because they can select any of the four ranges there are 4! > 24 possible permutations; so that's either one really long switch statement or numerous where clauses. I'm more than happy to take suggestions on the range thing; just a way to dynamically create a range without causing issues. Though one initial problem will be if a user selects 0-.3 .3-.6 and .9<;

Simplifying down to 0-.6 and .9<;
Multiple where clauses again.

Thankyou very much for your assistance with my syntax errors though!

[Old Pedant]

Naw, leave it alone. The OR conditions will be fast enough. No real point insimplifying given what you said.