Our solutions fall into the category of "Relational Division". In both
solutions, we allow for a remainder. What you want is exact division.
Here's a solution for exact division:

SELECT a.B
FROM @a a
left
join @a b on b.C = a.C
and b.D = a.D
and b.B = 200
where a.B <> 200
group by
a.B
having
count (distinct a.D) = (select count (distinct D) from @a where B = 200)

If A is an identity, you could use count (distinct A) where applicable.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@gmail.com> wrote in message
news:1144792950.602964.118760@g10g2000cwb.googlegr oups.com...
DECLARE @a TABLE(A int IDENTITY(1,1) PRIMARY KEY, B int, C int, D int);


INSERT INTO @a (B, C, D) VALUES (100, 30, 1)
INSERT INTO @a (B, C, D) VALUES (100, 30, 2)
INSERT INTO @a (B, C, D) VALUES (100, 30, 3)
INSERT INTO @a (B, C, D) VALUES (100, 40, 4)
INSERT INTO @a (B, C, D) VALUES (100, 40, 5)
INSERT INTO @a (B, C, D) VALUES (200, 30, 1)
INSERT INTO @a (B, C, D) VALUES (200, 30, 2)
INSERT INTO @a (B, C, D) VALUES (200, 30, 3)
INSERT INTO @a (B, C, D) VALUES (200, 40, 4)
INSERT INTO @a (B, C, D) VALUES (200, 40, 5)
INSERT INTO @a (B, C, D) VALUES (300, 30, 1)
INSERT INTO @a (B, C, D) VALUES (300, 30, 2)
INSERT INTO @a (B, C, D) VALUES (300, 40, 3)
INSERT INTO @a (B, C, D) VALUES (400, 40, 4)
INSERT INTO @a (B, C, D) VALUES (400, 40, 5)
INSERT INTO @a (B, C, D) VALUES (500, 30, 1)
INSERT INTO @a (B, C, D) VALUES (500, 30, 2)
INSERT INTO @a (B, C, D) VALUES (500, 30, 3)
INSERT INTO @a (B, C, D) VALUES (500, 40, 4)
INSERT INTO @a (B, C, D) VALUES (500, 40, 5)
INSERT INTO @a (B, C, D) VALUES (500, 31, 6)

--SELECT * FROM @a

DECLARE @i INT ;
SET @i = 200 ;

-- solution

The above solutions return 500 even though it contains _6_ records and
200 contains 5 records.