asked on

How to use multiple AND clauses together

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

SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DAN2011

ASKER

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.

Qlemo

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

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DAN2011

ASKER

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

Qlemo

(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.