criteria based on mulitple columns

[mmohon]

I have 4 columns, each can have a value of either B, D or E. I want to
exclude all records with a "D" in any column, and all records where
all 4 columns have a B

SELECT CHF.BILLNUMBER, CHF.HF1, CHF.HF2, CHF.HF3, CHF.HF4
FROM CHF
WHERE (((CHF.HF1)<>"D") AND ((CHF.HF2)<>"D") AND ((CHF.HF3)<>"D") AND
((CHF.HF4)<>"D"));

That is what I have so far, and that gets rid of all the D's like I
need, I just cant figure out how to exclude records where they have B's
for all 4 columns.


Is it possible?

 

[Smartin]

I have 4 columns, each can have a value of either B, D or E. I want to
exclude all records with a "D" in any column, and all records where
all 4 columns have a B

SELECT CHF.BILLNUMBER, CHF.HF1, CHF.HF2, CHF.HF3, CHF.HF4
FROM CHF
WHERE (((CHF.HF1)<>"D") AND ((CHF.HF2)<>"D") AND ((CHF.HF3)<>"D") AND
((CHF.HF4)<>"D"));

That is what I have so far, and that gets rid of all the D's like I
need, I just cant figure out how to exclude records where they have B's
for all 4 columns.


Is it possible?

Add to your WHERE clause:
OR (CHF.HF1='B' AND CHF.HF2='B' AND ... etc. )

This response would not be complete without a comment about the table
design. It's not a good idea to store repeating groups of data in the
same table. It leads to headaches like the one you have. Google on
"database normalization" (with the quotes) for lots more on this topic.

HTH

 

[David Cox]

I think it is desired to exclude all B's:-

OR NOT (CHF.HF1='B' AND CHF.HF2='B' AND ... etc. )

 

[Smartin]

I think it is desired to exclude all B's:-

OR NOT (CHF.HF1='B' AND CHF.HF2='B' AND ... etc. )

We're both wrong. Let's start over.

No D's anywhere:
CHF.HF1<>'D' AND CHF.HF2<>'D' AND CHF.HF3<>'D' AND CHF.HF4<>'D' {P1}

Not B's everywhere:
NOT (CHF.HF1='B' AND CHF.HF2='B' AND CHF.HF3='B' AND CHF.HF4='B') {P2}
alternatively,
CHF.HF1<>'B' OR CHF.HF2<>'B' OR CHF.HF3<>'B' OR CHF.HF4<>'B' {P2'}

So,
WHERE {P1} AND {P2}
Becomes
WHERE
(CHF.HF1<>'D' AND CHF.HF2<>'D' AND CHF.HF3<>'D' AND CHF.HF4<>'D')
AND NOT (CHF.HF1='B' AND CHF.HF2='B' AND CHF.HF3='B' AND CHF.HF4='B')

alternatively,
WHERE
(CHF.HF1<>'D' AND CHF.HF2<>'D' AND CHF.HF3<>'D' AND CHF.HF4<>'D')
AND (CHF.HF1<>'B' OR CHF.HF2<>'B' OR CHF.HF3<>'B' OR CHF.HF4<>'B')