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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
