If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

SQL Giving where clause dominance?

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;

Code:

"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?

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

Code:

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

Code:

( 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

Code:

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

Code:

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

Code:

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

Code:

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

and never had to worry about the OR conditions.

An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.

Users who have thanked Old Pedant for this post:

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<;