Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Why doesn't this work: A AND (<>B AND <>C)

    It's been a long week and I am having a complete brain lapse. I have a simple query that is not acting how i want it to:

    Code:
    SELECT * FROM proddw.dbo.vwdmerchandisecategory b
    WHERE MerchSubCatDesc like '%rug%'
    AND (b.merchcatcd <> 'o' AND b.MerchSubCatCd <> 'w')
    I am trying to pull records that contain the word 'RUG' and dont have a combination merchcatcd/merchsubcatcd of O/W. But this query is kicking out anything with an O and anything with a W, rather than looking for the combination. I thought it would evaluate the parenthesis first?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm 99% sure that you actually want:
    Code:
    SELECT *
       FROM proddw.dbo.vwdmerchandisecategory b
       WHERE MerchSubCatDesc like '%rug%'
          AND NOT (b.merchcatcd = 'o' AND b.MerchSubCatCd = 'w')
    This will find the rows where O/W applies, then exclude them from the result set.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    that'll do it, thank you.

  4. #4
    Join Date
    Mar 2004
    Posts
    162
    Quote Originally Posted by Pat Phelan View Post
    I'm 99% sure that you actually want:
    Code:
    SELECT *
       FROM proddw.dbo.vwdmerchandisecategory b
       WHERE MerchSubCatDesc like '%rug%'
          AND NOT (b.merchcatcd = 'o' AND b.MerchSubCatCd = 'w')
    This will find the rows where O/W applies, then exclude them from the result set.

    -PatP

    What is the differens on use "b.MerchSubCatCd = 'w'" and "b.MerchSubCatCd LIKE 'w'" ?
    I have a habbit of using LIKE when it comes to strings and = when dealing with numbers. Does it matter what to use?

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by mrpcguy View Post
    What is the differens on use "b.MerchSubCatCd = 'w'" and "b.MerchSubCatCd LIKE 'w'" ?
    I have a habbit of using LIKE when it comes to strings and = when dealing with numbers. Does it matter what to use?
    From StackOverflow:

    The equals (=) operator is a "comparison operator compares two values for equality." In other words, in an SQL statement, it won't return true unless both sides of the equation are equal. For example:

    SELECT * FROM Store WHERE Quantity = 200;
    The LIKE operator "implements a pattern match comparison" that attempts to match "a string value against a pattern string containing wild-card characters." For example:

    SELECT * FROM Employees WHERE Name LIKE 'Chris%';
    LIKE is generally used only with strings and equals (I believe) is faster. The equals operator treats wild-card characters as literal characters. The difference in results returned are as follows:

    SELECT * FROM Employees WHERE Name = 'Chris';
    And

    SELECT * FROM Employees WHERE Name LIKE 'Chris';
    Would return the same result, though using LIKE would generally take longer as its a pattern match. However,

  6. #6
    Join Date
    Mar 2004
    Posts
    162
    thanks for your reply even tho i hijacked your thread

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by mrpcguy View Post
    thanks for your reply even tho i hijacked your thread
    no worries!

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Pat Phelan View Post
    I'm 99% sure that you actually want:
    Code:
    SELECT *
       FROM proddw.dbo.vwdmerchandisecategory b
       WHERE MerchSubCatDesc like '%rug%'
          AND NOT (b.merchcatcd = 'o' AND b.MerchSubCatCd = 'w')
    This will find the rows where O/W applies, then exclude them from the result set.

    -PatP
    The sructure of the logic was
    A AND NOT(B AND C)

    It is equivalen to
    A AND (NOT B OR NOT C)

    So, the conditions in WHERE clause may be equivalent to
    Code:
    ...
       WHERE MerchSubCatDesc like '%rug%'
          AND (b.merchcatcd <> 'o' OR b.MerchSubCatCd <> 'w')

Posting Permissions

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