MS Access Select Query SQL with OR in two different fields & AND

Can any one correc the following MS Access SQL. The output should result in dataset
that would meet the criteria of rrcode field = 1 OR osreportcode =1 & AND with other field criteria.  The following code is not giving the desired output

string sqldemolablh = "SELECT * from tbldemolab WHERE freezecode=1  AND wtcode=0 AND (rrcode=1 OR osreportcode=1)  order by pid ";
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kent DyerConnect With a Mentor IT Security Analyst SeniorCommented:
-- PERFORMANCE TIP: RUN YOUR "UNUSUAL" TESTS FIRST TO FLUSH OUT INCORRECT RESULTS.
I think this what you are asking for..

Think of it this way:  Tommy and Betty and Suzy - so the condition has to be where all three are in the result set (limiting results).
Tommy or Betty or Suzy - so the condition has to be where all there own independent result set (expanding results).

You are asking..

Tommy and Betty and Suzy
or
David and Betty and Suzy

the results are going to be with two different sets: Tommy, Betty, Suzy in the first set as well as: David, Betty, Suzy

Let's re-write your query:
SELECT *
FROM   tbldemolab
WHERE  ( ( rrcode = 1
           AND freezecode = 1
           AND wtcode = 0 )
          OR ( osreportcode = 1
               AND freezecode = 1
               AND wtcode = 0 ) )
ORDER  BY pid  

Open in new window

0
 
COACHMAN99Connect With a Mentor Commented:
"SELECT * from tbldemolab WHERE ( freezecode=1  AND wtcode=0 AND rrcode=1) OR (freezecode=1  AND wtcode=0 AND osreportcode=1)  order by pid ";
0
 
IrogSintaCommented:
Your SQL statement looks fine except for the semicolon you have after the ending quote.  You should put the semicolon inside the quoted string or dispense with it altogether.
string sqldemolablh = "SELECT * from tbldemolab WHERE freezecode=1 AND wtcode=0 AND (rrcode=1 OR osreportcode=1) order by pid;"

Open in new window

Ron
0
 
Srinivas ManthaConsultant Anesthesiologist and Pain PhysicianAuthor Commented:
Prompt and timely solution. That semicolon is actually an error here. I was coding through ASP.NET c# defining the string for SQL. Now the code is
sqldemolablh = " SELECT * FROM   tbldemolab WHERE  ( ( rrcode = 1 AND freezecode = 1  AND wtcode = 0 )  OR ( osreportcode = 1 AND freezecode = 1  AND wtcode = 0 ) ) ORDER  BY pid ";
0
 
IrogSintaCommented:
I don't understand why the change was needed. Both WHERE comparisons are logically equivalent.  Look at these 2 sentences:

Can Betty and Suzy and (Tommy or David) join our team?
Can (Betty and Suzy and Tommy) or (Betty and Suzy and David) join our team?

Ron
0
All Courses

From novice to tech pro — start learning today.