Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    27

    Unanswered: Logical Operators in SQL statement

    I want to display all valid applications based on the SQL statement below. However, it returns none even though there are 2 valid applications in the database.

    Code:
    newLoanSQL = "SELECT LOAN_ID, DEPENDANT_CHILDREN, RESIDENTIAL_STATUS, LOAN_AMOUNT, INTEREST_RATE, DURATION, REPAYMENTS, REPAYMENT_AMOUNT, PURPOSE, OCCUPATION, EMPLOYMENT_STATUS, EMPLOYERS_NAME, ANNUAL_INCOME, SPOUSE_NAME, GUARANTOR, SECURITY, APPROVAL_ONE, APPROVAL_TWO, APPROVAL_THREE FROM LOAN_DETAILS Where NEW_APPLICATION = '" & searchString & "' AND APPROVER1_ID <> '" & id & "' OR APPROVER2_ID <> '" & id & "' OR APPROVER3_ID <> '" & id & "'"
    The SQL statement was working fine until I added the following:
    AND APPROVER1_ID <> '" & id & "' OR APPROVER2_ID <> '" & id & "' OR APPROVER3_ID <> '" & id & "'

    Can anyone help me please?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Logical Operators in SQL statement

    If you mix ANDs and ORs like that you are likely to get the wrong result due to the order in which they get processed. Bracket the ORs together like this:

    Where NEW_APPLICATION = ? AND (APPROVER1_ID <> ? OR APPROVER2_ID <> ? OR APPROVER3_ID <> ?)

    But is that really what you want? I imagine what you meant was really:

    Where NEW_APPLICATION = ? AND (APPROVER1_ID <> ? AND APPROVER2_ID <> ? AND APPROVER3_ID <> ?)

    i.e. "the given ID must not appear in ANY of the 3 approver_id columns", rather than "the given ID must not appear in ALL 3 of the approver_id columns (but can appear in 1 or 2 of them)"

    Note: I am encouraging you to change to bind variables at the same time. Not only do they make the code more readable and reduce typing errors, they also improve the performance and security of your application.

  3. #3
    Join Date
    Jan 2004
    Posts
    27
    I did as suggested by Tony Andrews. However, the SQL statement still is not working.

    Code:
    newLoanSQL = "SELECT LOAN_DETAILS.LOAN_ID, LOAN_DETAILS.DEPENDANT_CHILDREN, LOAN_DETAILS.RESIDENTIAL_STATUS, LOAN_DETAILS.LOAN_AMOUNT, LOAN_DETAILS.INTEREST_RATE, LOAN_DETAILS.DURATION, LOAN_DETAILS.REPAYMENTS, LOAN_DETAILS.REPAYMENT_AMOUNT, LOAN_DETAILS.PURPOSE, LOAN_DETAILS.OCCUPATION, LOAN_DETAILS.EMPLOYMENT_STATUS, LOAN_DETAILS.EMPLOYERS_NAME, LOAN_DETAILS.ANNUAL_INCOME, LOAN_DETAILS.SPOUSE_NAME, LOAN_DETAILS.GUARANTOR, LOAN_DETAILS.SECURITY, LOAN_DETAILS.APPROVAL_ONE, LOAN_DETAILS.APPROVAL_TWO, LOAN_DETAILS.APPROVAL_THREE, LOAN_DETAILS.NEW_APPLICATION, LOAN_DETAILS.APPROVER1_ID, LOAN_DETAILS.APPROVER2_ID, LOAN_DETAILS.APPROVER3_ID FROM LOAN_DETAILS WHERE LOAN_DETAILS.NEW_APPLICATION='Yes' AND (APPROVER1_ID <> '" & sID & "' AND APPROVER2_ID <> '" & sID & "' AND APPROVER3_ID <> '" & sID & "') OR (LOAN_DETAILS.APPROVER1_ID Is Null AND LOAN_DETAILS.APPROVER2_ID Is Null AND LOAN_DETAILS.APPROVER3_ID Is Null)"
    Please, can someone help me with this as I am working to a deadline.
    Thanks in advance

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    NULLs are a problem in SQL too, since they are neither equal to, nor not equal to, anything - including NULL! So perhaps what you need is:

    ((APPROVER1_ID <> ? OR APPROVER1_ID IS NULL)
    AND (APPROVER2_ID <> ? OR APPROVER2_ID IS NULL)
    AND (APPROVER3_ID <> ? OR APPROVER3_ID IS NULL))

  5. #5
    Join Date
    Jan 2004
    Posts
    27

    Talking

    All working now! Thanks a million, I really appreciate your help.
    Cheers

Posting Permissions

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