Logical Operators in SQL statement

lklegend · #1 (**permalink**) 02-25-04, 15:15

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?

andrewst · #2 (**permalink**) 02-26-04, 07:29

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.

lklegend · #3 (**permalink**) 02-26-04, 12:51

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

andrewst · #4 (**permalink**) 02-26-04, 14:12

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))

lklegend · #5 (**permalink**) 02-26-04, 16:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech