Suppose 1=Red, 2=Yellow, 4=Green and 8=Orange.
Now suppose the following data structures and rows exist:
create table coloursample (recid integer, colour integer, descript varchar);
insert into coloursample values (1,2,'Yellow only');
insert into coloursample values (2,10,'Yellow and Orange');
insert into coloursample values (3,11,'Red, Yellow and Orange');
insert into coloursample values (4,12,'Green and Orange');
insert into coloursample values (5,13,'Red, Green and Orange');
Selecting things which have some yellow in them somewhere is easy:
select * from coloursample where colour & 2>0;
It's also easy to find records which have either some yellow or some
orange (or both) in them:
select * from coloursample where colour & 10>0;
But how do I find records which are ONLY yellow and orange, and
exclude records which have some other colour mixed in, in one simple
query without a lot of 'not this, not that' additions, and without
using multiple separate AND tests to nail it down?
I thought to do
select * from coloursample where colour & 10 = 10;
...but that's not right, because it finds the third record is a match.
(The question comes about because I'm trying to test for up to 15
attributes per record. One record has a bitmask value of 21205, say.
That should mean the '1' bit is set (because 21205 =
1+4+16+64+128+512+4096+16384), but when I do the above queries for
21205 & 4098 (which is 4096 + 2, and therefore should not be finding
records with the '1' bit set), the record is being returned because
the 4096 bit is being detected (correctly) as 'on'. I want the query
to only return records where both bits are true, but I don't want to
have to test the records 15 times to find out!).
I suspect the answer is really simple... but I'm having writer's block
today! All help appreciated.
Regards
HJR