438,806 Members | 1,837 Online Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

# find matching sets of rows

 P: n/a 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 Apr 11 '06 #1
7 Replies

 P: n/a figital wrote: 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 Thanks for posting the sample data. It really does help however if you include KEYS with your DDL. Your table doesn't seem to have a key - all the columns are nullable. That may make your problem a lot harder to solve. Assuming you can rewrite the table variable as: DECLARE @a TABLE(A int, B int, C int, D int, PRIMARY KEY (b,c,d)); Then you can do: DECLARE @i INT ; SET @i = 100 ; SELECT B.b FROM @a AS A JOIN @a AS B ON A.b = @i AND A.c = B.c AND A.d = B.d AND B.b <> @i GROUP BY B.b HAVING COUNT(*)= (SELECT COUNT(*) FROM @a WHERE b = @i); If I'm wrong and you don't have such a key then it's not clear how you want to handle duplicates. Here's a different example, assuming that A is the key and that duplicates are significant, i.e. you want the same number of rows in each set identified by column B: SELECT B.b FROM @a AS A JOIN @a AS B ON A.b = @i AND A.c = B.c AND A.d = B.d AND B.b <> @i GROUP BY B.b HAVING COUNT(DISTINCT A.a)= (SELECT COUNT(DISTINCT a) FROM @a WHERE b = @i); Hope this helps. -- David Portas, SQL Server MVP Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages. SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx -- Apr 11 '06 #2

 P: n/a 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" wrote in message news:11**********************@i40g2000cwc.googlegr oups.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 Apr 11 '06 #3

 P: n/a The key is Column A (ident). Sorry for not providing more complete ddl. I will check out these suggestions, thanks! Apr 11 '06 #4

 P: n/a 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. Apr 11 '06 #5

 P: n/a 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" wrote in message news:11**********************@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. Apr 11 '06 #6

 P: n/a Tom and David, Thank you very much for your help! I had to add a check because of the left join but otherwise, awesome! SELECT a.B FROM @a a left join @a b on b.C = a.C and b.D = a.D and b.B = @i where a.B <> @i and NOT (B.C IS NULL OR B.D IS NULL) group by a.B having count (distinct a.D) = (select count (distinct D) from @a where B = @i) Apr 11 '06 #7

 P: n/a Oh, OK. The input data didn't have nulls, so I didn't go there. Glad you now have a solution. :-) -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Toronto, ON Canada .. "figital" wrote in message news:11**********************@g10g2000cwb.googlegr oups.com... Tom and David, Thank you very much for your help! I had to add a check because of the left join but otherwise, awesome! SELECT a.B FROM @a a left join @a b on b.C = a.C and b.D = a.D and b.B = @i where a.B <> @i and NOT (B.C IS NULL OR B.D IS NULL) group by a.B having count (distinct a.D) = (select count (distinct D) from @a where B = @i) Apr 12 '06 #8

### This discussion thread is closed

Replies have been disabled for this discussion. 