Hi
I was wondering if anyone could help me with the problem of using one recordset's results to filter another recordset?
The page that I am trying to create shows the results from a table one record at a time and I am trying to get it to exclude any records that match records in another recordset. I am using ultradev 4 and the script is VBScript.

-- this was taken from an unreplied thread a page back, and im having the same problem...

any help?

What are the results shown, text or numeric?

Are the 2 recordsets created from the same table?

What does the SQL statement look like for the first recordset?

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
What are the results shown, text or numeric?

Are the 2 recordsets created from the same table?

What does the SQL statement look like for the first recordset?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Ok my posistion is, i have two tables: Orders and Products. I filter the orders table by a UserID session variable (numeric). I then want to filter the products recordset by a field in the orders table (productID) so i can get the current price from one table and also the static price from the other (price comparison thingy)

for the first recordset the SQL is

SELECT *
FROM tblOrders
WHERE orderUserID = strUserID

strUserID 1 Session("svUserID"



all that works fine. i now want to filter the products recordset with the 'productID' field avaliable in both recordsets...

all fields are numeric btw, i case it wasnt clear.

--this sounds fairly doable from this side...

any help would be great, thanks.

unless your using mySQL, try using a subquery... something like<pre id=code><font face=courier size=2 id=code>SELECT * FROM Products WHERE prodid IN
(SELECT prodid FROM orders where userid = mmUserid)</font id=code></pre id=code>or something like that

Joel Martinez [ ]
----------
set rs = conn.execute("SELECT answer FROM brain WHERE question = "& forumPost &"
'2nd place is just 1st Loser
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Hi
I was wondering if anyone could help me with the problem of using one recordset's results to filter another recordset?


any help?


<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>


is this what your looking for ?


<pre id=code><font face=courier size=2 id=code>



Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
if (Request.QueryString("ID" &lt;&gt; "" then Recordset1__MMColParam = Request.QueryString("ID"

set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_nhs_STRING
Recordset1.Source = "SELECT * FROM orgtype WHERE ID = " + Replace(Recordset1__MMColParam, "'", "''" + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open()
Recordset1_numRows = 0




Dim Recordset2__MMColParam
Recordset2__MMColParam = (Recordset1.Fields.Item("ID".Value)
if (Request("MM_EmptyValue" &lt;&gt; "" then Recordset2__MMColParam = Request("MM_EmptyValue"

set Recordset2 = Server.CreateObject("ADODB.Recordset"
Recordset2.ActiveConnection = MM_nhs_STRING
Recordset2.Source = "SELECT * FROM reg WHERE ID = " + Replace(Recordset2__MMColParam, "'", "''" + ""
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 3
Recordset2.Open()
Recordset2_numRows = 0

Recordset1.Close()

Recordset2.Close()

</font id=code></pre id=code>

Thanks
suhailkaleem