Posted by figital on 04/11/06 23:44

Given an ID (column B), I need to find which IDs have identical data.

That is, given '200', I want the desired result to be:
100

The idea is that the system sees that id=200 has 5 records with the
indicated data in cols C and D.

It should then find any other ids with the exact same data for those
columns.

Note, in this case, both 200 and 100 have (30:1, 30:2, 30:3, 40:4,
40:5) so they match. 300 and 400 should NOT be returned.

Any bright ideas out there? Thanks!


DECLARE @a TABLE(A int, B int, C int, D int)
DECLARE @b TABLE(A int, B int, C int, D int)

INSERT INTO @a (A, B, C, D) VALUES (1, 100, 30, 1)
INSERT INTO @a (A, B, C, D) VALUES (2, 100, 30, 2)
INSERT INTO @a (A, B, C, D) VALUES (3, 100, 30, 3)
INSERT INTO @a (A, B, C, D) VALUES (4, 100, 40, 4)
INSERT INTO @a (A, B, C, D) VALUES (5, 100, 40, 5)

INSERT INTO @a (A, B, C, D) VALUES (6, 200, 30, 1)
INSERT INTO @a (A, B, C, D) VALUES (7, 200, 30, 2)
INSERT INTO @a (A, B, C, D) VALUES (8, 200, 30, 3)
INSERT INTO @a (A, B, C, D) VALUES (9, 200, 40, 4)
INSERT INTO @a (A, B, C, D) VALUES (10, 200, 40, 5)

INSERT INTO @a (A, B, C, D) VALUES (11, 300, 30, 1)
INSERT INTO @a (A, B, C, D) VALUES (12, 300, 30, 2)
INSERT INTO @a (A, B, C, D) VALUES (13, 300, 40, 3)
INSERT INTO @a (A, B, C, D) VALUES (14, 400, 40, 4)
INSERT INTO @a (A, B, C, D) VALUES (15, 400, 40, 5)

SELECT * FROM @a

[Back to original message]