Search Duplicates

Hi,


I have 4 tables :


Code Block
SAMPLE
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);

Create Table #Dup ([dupid] int , [cname] Varchar(30), [cityno] 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','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','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 #Dup VALUES (1, 'Bobby',12);
Insert Into #Dup VALUES (2, 'Harry',12);
Insert Into #Dup VALUES (3, 'Timothy',14);
Insert Into #Dup VALUES (4, 'Guck',44);
Insert Into #Dup VALUES (5, 'Angela',44);






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 found and pass it to Dup table to search for any duplicates.
4. If found return those duplicates:


Eg:

I pass in CallDetailID as 6532.
There are 2 records in Call table for this i.e (114,123). From this i get the request rows

456456','Bobby','SA024',12,114,0
'22322362','Guck','SA024',44,123,0


After getting these 2 #Request rows, i have to pass the CustomerName and CityNo of these 2 request rows to the #DUP table to see if there is similar data present. If yes return the rows to the client.


OUTPUT should be

1, 'Bobby',12
4, 'Guck',44


#Dup can contain millions of rows. Please suggest how to do this. Please give t-sql statement.

thanks a ton.