CODEWITHSUNDEEP
sqlsql-server-ce
cjv
cjv

Reputation: 49

Query based on more than 1 field

not sure how to title this but here is my scenario

working on Webmatrix, built in sqlCE DB.

Have a table with multiple bit fields (3 fields)

what i need is to search for all records but skip those where any one of these three bit fields is False

my query looks like this

Select * From Tr where Tr.ClID=28 and (VApproved<>'False' or PApproved<>'False' or CApproved<>'False')

Now if i search for ClID=28 i get 7 records and with the above query also get 7 records. Se basically the second part of the where condition is not working.

Appreciate your help and assistance.

Thanks,

Addition Data Looks like this

ClID    VApproved    PApproved    CApproved
****    *********    *********    *********
28      True         True         True
28      True         True         True
28      NULL         True         True
28      NULL         True         True
28      NULL         True         True
28      NULL         True         True
28      NULL         False        True

Upvotes: 0

Views: 135

Answers (2)

pabdulin
pabdulin

Reputation: 35245

If you need to skip any with False value, then you it's just your condition that isn't correct, it should be:

Select * From Tr where Tr.ClID=28 and VApproved<>'False' and PApproved<>'False' and CApproved<>'False'

i.e. all of them must not be false, so we use AND not OR. And considering your NULL requirement it should be like this:

Select * From Tr where Tr.ClID=28 and (VApproved=1 OR VApproved IS NULL) and (PApproved=1 OR PApproved IS NULL) and (CApproved=1 OR PApproved IS NULL)

Upvotes: 1

jpw
jpw

Reputation: 44911

I think you might have the condition wrong. This:

and (VApproved<>'False' or PApproved<>'False' or CApproved<>'False')

says to get rows where any (or all) of the fields is not false, meaning a row with some (or all) true will be a match. You probably want this:

(coalesce(VApproved,1) & coalesce(PApproved,1) & coalesce(CApproved,1)) = 1

Using bitwise logical and & together with coalesce() makes sure only rows where all columns are true or null are returned. This will return all rows in your sample data except the row where PApproved is False.

If you just want the rows with all true values (treating null as false) then:

where (VApproved & PApproved & CApproved) = 1

will do it. This will return just the first two rows in the sample.

Upvotes: 0

Related Questions