Posted by Tom Moreau on 04/12/06 00:08

Try:

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


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"figital" <mharen@gmail.com> wrote in message
news:1144788286.896210.141080@i40g2000cwc.googlegroups.com...
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

• Next in forum: Re: Getting all nodes and node details from xml using xquery
• Prev in forum: find matching sets of rows
• Thread view: Re: find matching sets of rows

[Reply to this message]