|
|
Posted by Tom Moreau on 04/12/06 01:43
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.googlegroups.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.
Navigation:
[Reply to this message]
|