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 @.afigital 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/...US,SQL.90).aspx
--|||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.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|||The key is Column A (ident). Sorry for not providing more complete ddl.
I will check out these suggestions, thanks!|||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.|||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.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.|||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)|||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" <mharen@.gmail.com> wrote in message
news:1144797933.088430.155480@.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)
