Finding duplicates inside a table

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 2005 Forums

Transact-SQL (2005)

Finding duplicates inside a table

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 04:18:34

Hi,

[Note: this is a seperate requirement with different table strucure. Hence a new post ]

I have 4 tables :

[Code]
SAMPLE

Create Table #Request ( [requestid] int , (30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);

Create Table #RequestDetail(reqdetailid int, [customername] Varchar, [customerage] int, requestid int);

Create Table #Call(Callid int,Calltype int,callDetailid int )

Create Table #CallDetail(callDetailId int,empid int)

Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)

Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)

Insert Into #request Values('324234','SA023',12,111,0);
Insert Into #request Values('223452','SA023',12,112,0);
Insert Into #request Values('456456','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);

Insert into #requestdetail values('Jack','324234')
Insert into #requestdetail values('Tom','223452')
Insert into #requestdetail values('Bobby','456456')
Insert into #requestdetail values('Guck','22322362')
Insert into #requestdetail values('Luck','22654392')

1. My stored proc will take the CallDetailID.

2. I have to find out the requests made on this calldetail.

3. After getting the request, i want to take the Customername, cityno of the request/requestdetail and pass it again to #request table to search for any duplicates within the request.

4. If found return the details of the original record :

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 04:42:34

Cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90611

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 05:04:39

:)

That is why i had put this note explicitly in the beginning to specify that this is not a cross post.

[Note: this is a seperate requirement with different table strucure. Hence a new post ].

Can you help me solve this?

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 05:14:00

Hi I made a error in the DDL of #request table. It needs to be like this

Insert Into #request Values('324234','SA023',12,111,0);
Insert Into #request Values('223452','SA023',12,112,0);
Insert Into #request Values('456456','SA024',12,114,0);
Insert Into #request Values('22322362','SA024',44,123,0);
Insert Into #request Values('22654392','SA023',12,134,0);

Insert into #requestdetail values('Jack','324234')
Insert into #requestdetail values('Tom','223452')
Insert into #requestdetail values('Bobby','456456')
Insert into #requestdetail values('Guck','22322362')
Insert into #requestdetail values('Jack','22654392')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 05:26:59

Just exchange the LAST INNER JOIN for the suggestion made in above link, from #Dup to #Request.
If you don't know how, here is the way.

SELECT		r2.CName,
		r2.CityNo
FROM		#Request AS r
INNER JOIN	#Call AS c ON c.CallID = r.CallID
INNER JOIN	#Request AS r2 ON r2.CName = r.CustomerName AND r2.CityNo = r.CityNo
WHERE		c.CallDetailID = 6532

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 05:47:09

Hi Peso,

thanks. r2.Cname will give an error as cname is no more in #request. It is in #requestdetail.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 05:55:24

I would suggest you change the column names to the ones you really want.
I can't do everything for you, can I?

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 06:09:13

Dear Peso,

There is a misunderstanding. So please read this carefully.

I am not referring to the column names at all. I am telling that cname or customername whatever we call it is not in #Request. It is in #RequestDetail. So the query will give an error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-08 : 06:11:22

OMG!

SELECT		rd.CName,
		rd.CityNo
FROM		#Request AS r
INNER JOIN	#Call AS c ON c.CallID = r.CallID
INNER JOIN	#Request AS r2 ON r2.CName = r.CustomerName AND r2.CityNo = r.CityNo
INNER JOIN	#RequestDetail AS rd ON rd.RequestID = r2.RequestID
WHERE		c.CallDetailID = 6532

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-10-08 : 06:25:36

:( thanks anyways.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources