locked
Simple SQL Query question RRS feed

  • Question

  • Hi,

    I have very little experience writing SQL Queries.  I am trying to use a query builder in a CRM tool called SalesLogix.  The query builder builds the following 2 queries:  The only difference being the "OR (UPPER(A1.TITLE) LIKE  '%OD %')"

    the first gives me expected results - the second gives me results from states other than MA or PA - which is the first condition.

    There must be a simple - logical - answer - but I can't see it.  Can anyone help?

    -bouchardpie

    SELECT A1.CONTACTID, A1.NAMELF, A2.STATE A2_STATE, A1.ACCOUNT, A1.TITLE, A1.LASTHISTORYDATE, A3.USERID A3_USERID, A1.NAME, A1.DONOTEMAIL, A1.DONOTSOLICIT FROM CONTACT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) LEFT OUTER JOIN ACTIVITY A3 ON (A1.CONTACTID=A3.CONTACTID) WHERE (A1.ACCOUNT IS NOT NULL AND A1.ACCOUNT <> '') AND (A2.STATE IN ('MA','PA')) AND ((UPPER(A1.TITLE) LIKE  '%LD %')) ORDER BY A1.LASTHISTORYDATE DESC 


    SELECT A1.CONTACTID, A1.NAMELF, A2.STATE A2_STATE, A1.ACCOUNT, A1.TITLE, A1.LASTHISTORYDATE, A3.USERID A3_USERID, A1.NAME, A1.DONOTEMAIL, A1.DONOTSOLICIT FROM CONTACT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) LEFT OUTER JOIN ACTIVITY A3 ON (A1.CONTACTID=A3.CONTACTID) WHERE (A1.ACCOUNT IS NOT NULL AND A1.ACCOUNT <> '') AND (A2.STATE IN ('MA','PA')) AND ((UPPER(A1.TITLE) LIKE  '%LD %')) OR (UPPER(A1.TITLE) LIKE  '%OD %') ORDER BY A1.LASTHISTORYDATE DESC 

    Thursday, August 29, 2013 7:07 PM

Answers

  • The formatting was hard to read, but basically AND has higher "operator precedence" than OR, meaning that your conditions mean:

    WHERE
        (
     (A1.ACCOUNT IS NOT NULL AND A1.ACCOUNT <> '')
    AND (A2.STATE IN ('MA','PA'))
    AND ((UPPER(A1.TITLE) LIKE  '%LD %'))
     )
    OR  (UPPER(A1.TITLE) LIKE  '%OD %')

    Where I assume you want below instead (note closely the brackets):

    WHERE
        (
     (A1.ACCOUNT IS NOT NULL AND A1.ACCOUNT <> '')
     AND (A2.STATE IN ('MA','PA'))
     )
    AND (
     (UPPER(A1.TITLE) LIKE  '%LD %')
     OR  (UPPER(A1.TITLE) LIKE  '%OD %')
     )


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Olaf HelperMVP Friday, August 30, 2013 6:33 AM
    • Marked as answer by Sofiya Li Wednesday, September 4, 2013 1:37 PM
    Thursday, August 29, 2013 7:48 PM
  • You can change the second query to this one:

    SELECT  A1.CONTACTID ,
            A1.NAMELF ,
            A2.STATE A2_STATE ,
            A1.ACCOUNT ,
            A1.TITLE ,
            A1.LASTHISTORYDATE ,
            A3.USERID A3_USERID ,
            A1.NAME ,
            A1.DONOTEMAIL ,
            A1.DONOTSOLICIT
    FROM    CONTACT A1
            INNER JOIN ADDRESS A2 ON ( A1.ADDRESSID = A2.ADDRESSID )
            LEFT OUTER JOIN ACTIVITY A3 ON ( A1.CONTACTID = A3.CONTACTID )
    WHERE   ( A1.ACCOUNT IS NOT NULL
              AND A1.ACCOUNT <> ''
            )
            AND ( A2.STATE IN ( 'MA', 'PA' ) )
            AND ( ( UPPER(A1.TITLE) LIKE '%LD %' )
                  OR ( UPPER(A1.TITLE) LIKE '%OD %' )
                )
    ORDER BY A1.LASTHISTORYDATE DESC 


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    • Proposed as answer by Olaf HelperMVP Friday, August 30, 2013 6:33 AM
    • Marked as answer by Sofiya Li Wednesday, September 4, 2013 1:36 PM
    Thursday, August 29, 2013 7:48 PM

All replies

  • You can change the second query to this one:

    SELECT  A1.CONTACTID ,
            A1.NAMELF ,
            A2.STATE A2_STATE ,
            A1.ACCOUNT ,
            A1.TITLE ,
            A1.LASTHISTORYDATE ,
            A3.USERID A3_USERID ,
            A1.NAME ,
            A1.DONOTEMAIL ,
            A1.DONOTSOLICIT
    FROM    CONTACT A1
            INNER JOIN ADDRESS A2 ON ( A1.ADDRESSID = A2.ADDRESSID )
            LEFT OUTER JOIN ACTIVITY A3 ON ( A1.CONTACTID = A3.CONTACTID )
    WHERE   ( A1.ACCOUNT IS NOT NULL
              AND A1.ACCOUNT <> ''
            )
            AND ( A2.STATE IN ( 'MA', 'PA' ) )
            AND ( ( UPPER(A1.TITLE) LIKE '%LD %' )
                  OR ( UPPER(A1.TITLE) LIKE '%OD %' )
                )
    ORDER BY A1.LASTHISTORYDATE DESC 


    The most important motivation for the research work that resulted in the relational model was the objective of providing a sharp and clear boundary between the logical and physical aspects of database management. - E. F. Codd


    My blog

    • Proposed as answer by Olaf HelperMVP Friday, August 30, 2013 6:33 AM
    • Marked as answer by Sofiya Li Wednesday, September 4, 2013 1:36 PM
    Thursday, August 29, 2013 7:48 PM
  • The formatting was hard to read, but basically AND has higher "operator precedence" than OR, meaning that your conditions mean:

    WHERE
        (
     (A1.ACCOUNT IS NOT NULL AND A1.ACCOUNT <> '')
    AND (A2.STATE IN ('MA','PA'))
    AND ((UPPER(A1.TITLE) LIKE  '%LD %'))
     )
    OR  (UPPER(A1.TITLE) LIKE  '%OD %')

    Where I assume you want below instead (note closely the brackets):

    WHERE
        (
     (A1.ACCOUNT IS NOT NULL AND A1.ACCOUNT <> '')
     AND (A2.STATE IN ('MA','PA'))
     )
    AND (
     (UPPER(A1.TITLE) LIKE  '%LD %')
     OR  (UPPER(A1.TITLE) LIKE  '%OD %')
     )


    Tibor Karaszi, SQL Server MVP | web | blog

    • Proposed as answer by Olaf HelperMVP Friday, August 30, 2013 6:33 AM
    • Marked as answer by Sofiya Li Wednesday, September 4, 2013 1:37 PM
    Thursday, August 29, 2013 7:48 PM
  • Like the other posters already said you have to be careful with your use of Logical Operators or you will not get correct results for what you need.  I would recommend referring to this article on the different kinds:  http://technet.microsoft.com/en-us/library/ms189773.aspx

    Also as a side note- When writing code in SQL especially I would recommend putting each command on a different line.  Even though it is read as one line when it is processed it is very hard to write and read this way.  

    One good practice to follow is to structure your select statements like this:  

    SELECT ...,

    ,   ....

    ,   ....

    ,   ....

    FROM ....

    ,    ....

    ,    ....

    WHERE (0=0)

    AND .....

    OR ....

    This allows you to comment out a line and have it not break your code.  Since 0 always equals 0 I always put it in the WHERE statement to make for easier edits.  

    • Proposed as answer by Olaf HelperMVP Friday, August 30, 2013 6:33 AM
    Thursday, August 29, 2013 7:57 PM