filtering a second rs with the filtered results of a first rs

[Zachariah Crow]

I am having trouble doing this. I want to use a filtered recordset to filter
a second recordset. For example, the results of the first rs are Institution
ID numbers. I want to use all the returned records to filter a second
recordset which gets values from a different table but contains the
Institution ID field as well.

Thanks for any help.

[Brandon Taylor]

One way to do it would be to put all of the IDs returned from the first
recordset into an array, and then you could use the IN clause to filter your
second recordset.

Hypothetical Example:

set rs = createobject("adodb.recordset")
rs.open "select ID, Institution from tbl_Institutions" , cn

if not rs.eof then
dim arr_ids
i = 0
while not rs.eof
if i = 0 then
arr_ids = rs("ID")
else
arr_ids = arr_ids & "," & rs("ID")
end if
i = i + 1
rs.movenext()
wend
end if

'now all of the ids are in an array and you can use it to filter your
second recordset

set rs2 = createobject("adodb.recordset")
rs2.open "select something from other_table where foreign_key in (" &
arr_ids & ")" , cn

end if

I haven't tested this, but it should get you going on the right path.
bT

"Zachariah Crow" <zcrow@shaw.ca> wrote in message
news:d5qt5q$du2$1@forums.macromedia.com...

>I am having trouble doing this. I want to use a filtered recordset to
>filter a second recordset. For example, the results of the first rs are
>Institution ID numbers. I want to use all the returned records to filter a
>second recordset which gets values from a different table but contains the
>Institution ID field as well.
>
> Thanks for any help.
>

[Zachariah Crow]

Hi Brandon and thanks for the help on this.

I just can't seem to get the array to work however. If I try and display the
array it is blank when I know there should be returned records.


"Brandon Taylor" <bt@btaylordesign.com> wrote in message
news:d5qvel$haq$1@forums.macromedia.com...

> One way to do it would be to put all of the IDs returned from the first
> recordset into an array, and then you could use the IN clause to filter
> your second recordset.
>
> Hypothetical Example:
>
> set rs = createobject("adodb.recordset")
> rs.open "select ID, Institution from tbl_Institutions" , cn
>
> if not rs.eof then
> dim arr_ids
> i = 0
> while not rs.eof
> if i = 0 then
> arr_ids = rs("ID")
> else
> arr_ids = arr_ids & "," & rs("ID")
> end if
> i = i + 1
> rs.movenext()
> wend
> end if
>
> 'now all of the ids are in an array and you can use it to filter your
> second recordset
>
> set rs2 = createobject("adodb.recordset")
> rs2.open "select something from other_table where foreign_key in (" &
> arr_ids & ")" , cn
>
> end if
>
> I haven't tested this, but it should get you going on the right path.
> bT
>
> "Zachariah Crow" <zcrow@shaw.ca> wrote in message
> news:d5qt5q$du2$1@forums.macromedia.com...

>>I am having trouble doing this. I want to use a filtered recordset to
>>filter a second recordset. For example, the results of the first rs are
>>Institution ID numbers. I want to use all the returned records to filter a
>>second recordset which gets values from a different table but contains the
>>Institution ID field as well.
>>
>> Thanks for any help.
>>

>
>

[Brandon Taylor]

Hmm, that's weird. Email me your page as a zip to [email]bt@btaylordesign.com[/email] and
I'll take a look at it for you.

bT

"Zachariah Crow" <zcrow@shaw.ca> wrote in message
news:d5r1c8$kdk$1@forums.macromedia.com...

> Hi Brandon and thanks for the help on this.
>
> I just can't seem to get the array to work however. If I try and display
> the array it is blank when I know there should be returned records.
>
>
> "Brandon Taylor" <bt@btaylordesign.com> wrote in message
> news:d5qvel$haq$1@forums.macromedia.com...

>> One way to do it would be to put all of the IDs returned from the first
>> recordset into an array, and then you could use the IN clause to filter
>> your second recordset.
>>
>> Hypothetical Example:
>>
>> set rs = createobject("adodb.recordset")
>> rs.open "select ID, Institution from tbl_Institutions" , cn
>>
>> if not rs.eof then
>> dim arr_ids
>> i = 0
>> while not rs.eof
>> if i = 0 then
>> arr_ids = rs("ID")
>> else
>> arr_ids = arr_ids & "," & rs("ID")
>> end if
>> i = i + 1
>> rs.movenext()
>> wend
>> end if
>>
>> 'now all of the ids are in an array and you can use it to filter your
>> second recordset
>>
>> set rs2 = createobject("adodb.recordset")
>> rs2.open "select something from other_table where foreign_key in (" &
>> arr_ids & ")" , cn
>>
>> end if
>>
>> I haven't tested this, but it should get you going on the right path.
>> bT
>>
>> "Zachariah Crow" <zcrow@shaw.ca> wrote in message
>> news:d5qt5q$du2$1@forums.macromedia.com...

>>>I am having trouble doing this. I want to use a filtered recordset to
>>>filter a second recordset. For example, the results of the first rs are
>>>Institution ID numbers. I want to use all the returned records to filter
>>>a second recordset which gets values from a different table but contains
>>>the Institution ID field as well.
>>>
>>> Thanks for any help.
>>>

>>
>>

>
>