let us suppose that we have a Web Page where we can look for customers by countryes. The web page gives as the chance to choose several countryes at the same time. We have multiple checkboxes with the name of the options (not only one).
let's create the table to star this example
CREATE TABLE [customers] (
[idreg] [int] IDENTITY (1, 1) NOT NULL ,
[countryid] [int] NULL ,
[data] [char] (10)
) ON [PRIMARY]
GO
Now we can insert some data
INSERT INTO customers(countryid, data) values (2,'customer1')
INSERT INTO customers(countryid, data) values (8,'customer2')
INSERT INTO customers(countryid, data) values (8,'customer3')
INSERT INTO customers(countryid, data) values (16,'customer4')
INSERT INTO customers(countryid, data) values (16,'customer5')
INSERT INTO customers(countryid, data) values (32,'customer6')
INSERT INTO customers(countryid, data) values (32,'customer7')
GO
We need to obtain for example the customers of the country 8 and 32 (8+32=40)
SELECT *
FROM customers
WHERE (countryid & 40 )>0
...and the customers from the contryes 16,32 and 2
(16+32+2=50)
SELECT *
FROM customers
WHERE (countryid & 50 )>0
in other way we used to do something like this
SELECT *
FROM customers
WHERE countryid IN (16,32,2)
The advantage of using "&" operator is that we only have one parameter to look for multiple values. We only have to sum the values of the countryid.
The county id must be values like (2,4,8,32,64,128,256...)
I hope you like that...and sorry for my english!!!
|