Solved

How to use multiple AND clauses together

Posted on 2011-09-13
316 Views
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;
``````
0
Question by:DAN2011
• 3
• 2

LVL 24

Assisted Solution

johanntagle earned 100 total points
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*/
``````

0

Author Comment

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 68

Expert Comment

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;
``````
0

LVL 68

Accepted Solution

Qlemo earned 400 total points
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;
``````
0

Author Comment

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;
``````
0

LVL 68

Expert Comment

``````(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)
``````
is exactly the same as
``````(b_num <= 0.700 OR m_num <= 0.700 OR d_num <= 0.700 OR n_num <= 0.700)
``````
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

Join & Write a Comment Already a member? Login.

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Read about achieving the basic levels of HRIS security in the workplace.
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

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

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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!