Page 1 of 1

"OR" not working in MYSQL SQL

Posted: Tue Apr 15, 2008 7:06 pm
by camarosource
What am I doing wrong?

Code: Select all

 
SELECT 
  SUBMISSION_ID, 
  FULL_NAME, 
  EMAIL, 
  CAMARO_YEAR, 
  CAMARO_MODEL, 
  SAID_TO_PURCHASE,
  ACTUALLY_PURCHASED, 
  WERE_PICS_UPLOADED, 
  EMAILED_NO_PICS_SENT, 
  ADMIN_COMMENTS, 
  GALLERY_CREATED, 
  EMAILED_NO_GALLERY, 
  WERE_PICS_GOOD_ENOUGH, 
  EMAILED_PICS_NOT_GOOD 
FROM 
  user_submissions 
WHERE 
  WERE_PICS_UPLOADED = 'YES'
  AND WERE_PICS_GOOD_ENOUGH = 'NO' 
  OR WERE_PICS_GOOD_ENOUGH = ' ' 
ORDER BY SUBMISSION_ID;
 

I want to do the following

- Select those tables from the user_submissions database WHERE WERE_PICS_UPLOADED = 'YES'. It would ONLY select those tables if the WERE_PICS_GOOD_ENOUGH field = 'NO' OR WERE_PICS_GOOD_ENOUGH = a BLANK entry..

What's wrong?

It seems to select the "WERE_PICS_UPLOADED = 'YES'" is not being read as it will select all those tables EVEN if the WERE_PICS_UPLOADED = is NOT "YES".

If I make it.

"SELECT SUBMISSION_ID, FULL_NAME, EMAIL, CAMARO_YEAR, CAMARO_MODEL, SAID_TO_PURCHASE,
ACTUALLY_PURCHASED, WERE_PICS_UPLOADED, EMAILED_NO_PICS_SENT, ADMIN_COMMENTS, GALLERY_CREATED, EMAILED_NO_GALLERY,
WERE_PICS_GOOD_ENOUGH, EMAILED_PICS_NOT_GOOD FROM user_submissions WHERE WERE_PICS_UPLOADED = 'YES'
AND WERE_PICS_GOOD_ENOUGH = 'NO' ORDER BY SUBMISSION_ID";

(removing "OR WERE_PICS_GOOD_ENOUGH = ' ' ") t then it WON'T select the tables for those where "WERE_PICS_GOOD_ENOUGH" is BLANK.

Re: "OR" not working in MYSQL SQL

Posted: Tue Apr 15, 2008 7:42 pm
by Christopher
Operators are evaluated left to right, and AND has a higher precedence than OR, so "A AND B OR C" is the same as "(A AND B) OR C". I think what you want is "A AND (B OR C)".

Re: "OR" not working in MYSQL SQL

Posted: Tue Apr 15, 2008 7:46 pm
by califdon
It is risky to use both AND and OR in any boolean notation without using parentheses. If I told you, "Please show me all the dogs AND show me all the cats OR show me all the pigs AND show me all the cows," that can be interpreted in several entirely contradictory ways:
  1. (dogs AND cats) OR (pigs AND cows)
  2. dogs AND (cats OR pigs) AND cows
  3. dogs AND (cats OR (pigs AND cows))
You need to analyze what you want the outcome to be and put parentheses around the boolean operators to give you that.