Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: SQL Query Error
08-24-2007, 06:28 PM #1
- Join Date
- Feb 2006
- Thanked 0 Times in 0 Posts
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?
08-24-2007, 06:41 PM #2
- Join Date
- Mar 2007
- Thanked 19 Times in 19 Posts
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%'
category = '1127200642358', subcategory = '121200613846', SKU LIKE '%124%'
category = '1127200642358', SKU LIKE '%123%'
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%'))"
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!To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs