Query needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query needed

Author

Topic

Saeed
Starting Member

39 Posts

Posted - 2002-12-13 : 00:10:34

I have a table
Key Code DESC USERID
------ ----- ----- ------
1 B x xdddd
1 C x xdss
1 p X X
2 A x Pxxx
2 K x aaaax
3 A X aaaaX
1 A B Bxxxx

I want to be able to pick up the records where they
have the same CODE for distinct Keys

i.e:
1 A B Bxxxx
2 A x Pxxx
3 A X aaaaX

How Do i acheive this?
Thanks

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-13 : 00:33:57

CREATE TABLE #Test
(
AKey int,
Code varchar(1),
ADESC varchar(1),
USERID varchar(5)
)

INSERT INTO #TEST (AKey, Code, ADESC, USERID) VALUES (1,'B', 'x', 'xdddd')
INSERT INTO #TEST (AKey, Code, ADESC, USERID) VALUES (1,'C', 'x', 'xdss')
INSERT INTO #TEST (AKey, Code, ADESC, USERID) VALUES (1,'P', 'X', 'X')
INSERT INTO #TEST (AKey, Code, ADESC, USERID) VALUES (2,'A', 'x', 'Pxxx')
INSERT INTO #TEST (AKey, Code, ADESC, USERID) VALUES (2,'K', 'x', 'aaaax')
INSERT INTO #TEST (AKey, Code, ADESC, USERID) VALUES (3,'A', 'X', 'aaaaX')
INSERT INTO #TEST (AKey, Code, ADESC, USERID) VALUES (1,'A', 'B', 'Bxxxx')

--if Akey, Code combo is not unique then

SELECT A.AKey, A.Code, A.ADESC, A.USERID
FROM
#TEST A INNER JOIN
(
SELECT C.Code
FROM
(
SELECT AKey, Code
FROM #TEST
GROUP BY Akey, Code
) C
GROUP BY C.Code
HAVING COUNT(*) > 1
) B ON A.Code = B.Code

--if Akey, Code combo is unique then
SELECT A.AKey, A.Code, A.ADESC, A.USERID
FROM
#TEST A INNER JOIN
(
SELECT Code
FROM #TEST
GROUP BY Code
HAVING COUNT(*) > 1
) B ON A.Code = B.Code

DROP TABLE #Test

Saeed
Starting Member

39 Posts

Posted - 2002-12-13 : 01:59:27

I ll try that on Monday.
Thanks ;)

Saeed
Starting Member

39 Posts

Posted - 2002-12-15 : 16:12:02

quote:

Thats what the query returned
1 A B B
1 B x x
1 C x x
1 p X X
2 A x x
2 K x x

SELECT A.Keycode, A.GRMN, A.Description, A.UserID
FROM txtGRMCodes A INNER JOIN
(SELECT Keycode
FROM [dbo].[txtGRMCodes]
GROUP BY Keycode
HAVING COUNT(*) > 1) B ON A.Keycode = B.Keycode

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-15 : 16:38:01

You didn't read the example carefully enough.

SELECT A.Keycode, A.GRMN, A.Description, A.UserID
FROM txtGRMCodes A INNER JOIN
(SELECT GRMN
FROM [dbo].[txtGRMCodes]
GROUP BY GRMN
HAVING COUNT(*) > 1) B ON A.GRMN = B.GRMN

Saeed
Starting Member

39 Posts

Posted - 2002-12-15 : 16:54:07

quote:

You are right - I did not-
Thanks
SELECT A.Keycode, A.GRMN, A.Description, A.UserID
FROM txtGRMCodes A INNER JOIN
(SELECT GRMN
FROM [dbo].[txtGRMCodes]
GROUP BY GRMN
HAVING COUNT(*) > 1) B ON A.GRMN = B.GRMN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources