Q: Smart Many to Many Query I have a list of item descriptions in a c# application. What I want is when I select 
1 or 2 or more item descriptions of that list (checkbox list) to predict via an sql query to a many to many table what my item is (minimizing each time the possible predictions);
For example
item 1: white,green,blue
item 2: white,red,cyan
item 3: red,blue,purple



*

*user should select from a check list 

*white->query will return item 1,2

*white&green->query will return only item 1


A: From your humble description of the problem, I suppose you want something like this:    
 CREATE TABLE items (
    item_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(100) NOT NULL
)

CREATE TABLE colors (
    color_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(100) NOT NULL
)

CREATE TABLE items_colors (
    item_id INT NOT NULL FOREIGN KEY REFERENCES items(item_id),
    color_id INT NOT NULL FOREIGN KEY REFERENCES colors(color_id),
    PRIMARY KEY(item_id, color_id),
)

INSERT INTO items(name) VALUES ('item 1')
INSERT INTO items(name) VALUES ('item 2')
INSERT INTO items(name) VALUES ('item 3')

INSERT INTO colors(name) VALUES ('white')
INSERT INTO colors(name) VALUES ('green')
INSERT INTO colors(name) VALUES ('blue')
INSERT INTO colors(name) VALUES ('red')
INSERT INTO colors(name) VALUES ('cyan')
INSERT INTO colors(name) VALUES ('purple')

INSERT INTO items_colors(item_id, color_id) VALUES (1, 1)
INSERT INTO items_colors(item_id, color_id) VALUES (1, 2)
INSERT INTO items_colors(item_id, color_id) VALUES (1, 3)

INSERT INTO items_colors(item_id, color_id) VALUES (2, 1)
INSERT INTO items_colors(item_id, color_id) VALUES (2, 4)
INSERT INTO items_colors(item_id, color_id) VALUES (2, 5)

INSERT INTO items_colors(item_id, color_id) VALUES (3, 3)
INSERT INTO items_colors(item_id, color_id) VALUES (3, 4)
INSERT INTO items_colors(item_id, color_id) VALUES (3, 6)

SELECT i.* 
FROM items i 
WHERE 2 = (
    SELECT COUNT(*)
    FROM items_colors ic
    JOIN colors c
    ON ic.color_id = c.color_id
    WHERE i.item_id = ic.item_id
    AND c.name IN ('white', 'green')
)

Within "IN" clause you should provide list of values that user has selected in the UI (you have to build list of parameters dynamically). You also have to provide number of elements that user has selected ("2" in my example solution).
So the query in application will look like this:
SELECT i.* 
FROM items i 
WHERE @count = (
    SELECT COUNT(*)
    FROM items_colors ic
    JOIN colors c
    ON ic.color_id = c.color_id
    WHERE i.item_id = ic.item_id
    AND c.name IN (@color1, @color2, ..., @colorN)
)

(Where @count is the number of @colorX parameters.)
