search rows having same data in different table

• lalit-450914

  SSC-Addicted

  Points: 462

  More actions

  October 7, 2007 at 2:06 am

  #175792

  Hi,

  I have 4 tables :

  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.

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  October 7, 2007 at 12:09 pm

  #739852

  Answered here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90392

  ---

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

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  October 7, 2007 at 9:07 pm

  #739879

  Sounds like you work in the telecom space, as do I.

  Feel free to contact me directly if you like. I would like to know a little more about the application of what you are doing here. There are many ways that you can accomplish this however some factors come into play such as;

  Is this a real-time lookup as CDR's are streamed?

  Is this for a report, a rating engine? It may make a difference where you actually do this lookup, perhaps embedding into existing processes somewhere is more logical.

  In any event feel free to write me and I would be happy to do some discovery with you.

  jwilliamsoh@gmail.com

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh