asked on

ASP how to inner two recordset

In ASP I have two record sets, Both record set has field called "CustomerID "
now How can I build third record set where rs1.CustomerID = rs2.CustomerID

Rs1 : All orders
Rs2 : All Customer

How can I write R3 as following
Select Rs1.*
from RS1
Inner Join RS2 On RS1.CustomerID = RS2.CustomerID

RS1 and RS2 are stored in different DB system therefore I cannot write the join directly.

godeschalk

You can first get all the id from Rs2 put them in a comma seperated string.
#commaseperatedstring# = "1,5,7,10,14"
than call Rs1 like select * from rs1 where CustomerID in(#commaseperatedstring#)

G_H

Two other ways...

ASP has it's own RecordSet object, you could build a custom RecordSet to hold the info:
set Result = Server.CreateObject("ADODB.recordset")
Result.Fields.Append "FieldName", adVarChar
Result.Fields.Append "CustID", adInteger

You could also create a Temporary Table in the database containing the orders, and populate it with the Customer info...

It depends how much of what you want...

GH

Bharat Guru

ASKER

Recordsets are already created. I cannot modify the sql for existing recordset becaseu one record set is result from DB2 and another recordset is from SQL Server. Now I want to add filter on recordset1 base on recordset2

godeschalk

set all id'sin in a comma serated string by using a while loop

and then use Rs1.filter = "Id in ("& string &")"

godeschalk

Oh and Rs1.filter = 0 removes the filter and you can set a other filter on it

ASKER CERTIFIED SOLUTION

G_H

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bharat Guru

ASKER

Thanks