SQL Query Error

I'm running a SQL query on an ASP search page as follows:

"Select * from product WHERE category = '1127200642358' AND subcategory = '121200613846' AND SKU LIKE '%123%' or SKU like '%124%'"

I get 4 products back, however:
- The first 2 products have the correct category, subcategory and SKU
- The second 2 products have the correct category, but the subcategory is 1127200642532 on one of them and 1127200642437 on the other one. Nothing close to the requested subcategory in the query.

Any ideas on why these last 2 products are showing up even though they don't match the search query?

It has to do with the LIKE statements on the SKUs. Since you are not defining them as individual units with parentheses, you are telling the database to find all matching entries with:

category = '1127200642358', subcategory = '121200613846', SKU LIKE '%123%'

AND

category = '1127200642358', subcategory = '121200613846', SKU LIKE '%124%'

AND

category = '1127200642358', SKU LIKE '%123%'

AND

category = '1127200642358', SKU LIKE '%124%'

Wrap your AND statements in parentheses. That will solve this problem.


strSQL = "Select * from product WHERE (category = '1127200642358' AND subcategory = '121200613846') AND ((SKU LIKE '%123%') OR (SKU like '%124%'))"


Couple of quick notes:

1) Always try your queries out on the database first, before putting them into your ASP. It will help you to find if there are errors and whether or not you have the correct syntax.

2) Try to refrain from using "SELECT *". Always define which fields you want to see, unless you absolutely need to see ALL fields. This has a big influence on your Query Optimization.

HTH!