Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2

Thread: SQL Query Error

  1. #1
    New Coder
    Join Date
    Feb 2006
    Posts
    62
    Thanks
    1
    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?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    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%'

    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.

    Code:
    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!
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •