|
|
Posted by David Portas on 04/12/06 00:12
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|