Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to use multiple AND clauses together

Posted on 2011-09-13
6
Medium Priority
?
324 Views
Last Modified: 2012-06-21
I am trying to use multiple and clauses together. I am not getting the desired results only the first and clause seems to run .

I would like ths statement to output data;

when bnum is greater than or  equal to 0.700 and br_name is >= 1 and/or  when m_num <= equal to 0.700 and/or br_name is >= 1, output  data when d_num is greater than or equal to 0.700 and/or br_name is >= 1 output  data.



select * 
from tmp_table
where 
     b_num <= 0.700 AND br_num >= 1
AND   m_num <= 0.700 AND br_num >= 1
AND   d_num <= 0.700 AND br_num >= 1
ORDER BY name;

Open in new window

0
Comment
Question by:DAN2011
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 400 total points
ID: 36532687
Based on your description your where clause should be:

where (b_num <= 0.700 AND br_num >= 1)  /* because you said "bnum is greater than or  equal to 0.700 AND br_name is >= 1"*/
OR br_num >= 1
OR m_num <= 0.700
OR d_num <= 0.700
/*then for the rest you said "AND/OR", which translates to an OR*/

Open in new window


Please clarify.  Thanks.
0
 

Author Comment

by:DAN2011
ID: 36532778
I know its confusing. I am trying to check to see if all three conditions exist. For example in plain english if

b_num is greater than or equal to 7 and br_num is greater than 1 output this data,  if d_num is also greater than or equal to 7 and br_num is greater than 1 output this data as well and if m_num is greater than or equal to 7 and br_num is greater than 1 output this data.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 36532795
As you are describing it, the main requirement is "br_num >= 1", and any of the other conditions. However, what you described is rather blurry. I would decode it as
select * from tmp_table
where br_num >= 1 and d_num <= 0.700)
   and (b_num <= 0.700 OR m_num <= 0.700)
ORDER BY name;

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 71

Accepted Solution

by:
Qlemo earned 1600 total points
ID: 36532810
After http:#a36532778 you really want to have EITHER condition to apply. That is
select * from tmp_table
where br_num >= 1 
   and (b_num <= 0.700 OR m_num <= 0.700 or d_num <= 0.700)
ORDER BY name;

Open in new window

0
 

Author Comment

by:DAN2011
ID: 36532939
I think this is more of what I am trying to do, but now the br_num >=1 is not working
select * 
from table
where br_num >= 1 
AND (b_num <= 0.700 OR m_num <= 0.700 OR d_num <= 0.700 OR n_num <= 0.700)
OR (m_num <= 0.700 OR b_num <= 0.700 OR d_num <= 0.700 OR n_num <= 0.700) 
OR (d_num <= 0.700 OR m_num <= 0.700 OR b_num <= 0.700 OR wtd_market_index <= 0.700)
OR (n_num<= 0.700 OR d_num <= 0.700 OR m_num <= 0.700 OR b_num <= 0.700)
ORDER BY name;

Open in new window

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 36532992
(b_num <= 0.700 OR m_num <= 0.700 OR d_num <= 0.700 OR n_num <= 0.700)
OR (m_num <= 0.700 OR b_num <= 0.700 OR d_num <= 0.700 OR n_num <= 0.700) 

Open in new window

is exactly the same as
(b_num <= 0.700 OR m_num <= 0.700 OR d_num <= 0.700 OR n_num <= 0.700)

Open in new window

so your conditions do not make sense. In addition, br_num >= 1 needs only to apply for the very first conditional expression, because of the AND; any other condition needs to be true with no respect to br_num, as you wrote it. AND has precedence over OR, so
    a AND b OR c     is in fact   (a AND b) OR C
Because of several algebraic rules (distributive and associative law)
    a OR (b OR c OR d)    is the same as   a OR b OR c OR d
and
   (a AND b) OR (a AND c)   is the same as   a AND (b OR c)

If you think of OR like + and AND like *, common arithmetic applies, and it is easier to transform.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question