Using Recordset !

[bomayed]

I will use the following example as a question:

This is a recordset
set rs=cn.execute("SELECT * from TABLE_1&quot

Now I want to select records from another table but where the ID matches.

set rs2=cn.exuecute("SELECT * from TABLE_2 where ID=&quot HERE IS WHERE I AM STUCK!

I don't want to say , (Where ID IN (SELECT ....), because I don't want to repeat the previous select statement. Instead I want to use the IDs stored in the first recordset!
Note: I want to select all the IDs , Not just one ID

Can someone tell me how to do that?

Thank you . .

 

[simonchristieis]

do you mean something like this

Do While Not rs.EOF


set rs2=cn.execute("SELECT * from TABLE_2 where ID= " & rs("myIDs&quot)

'display information from rs2 then

rs.movenext
loop


hope this helps

simon

 

[logicalunatic]

Try this...

<%
'I'll assume that your connection to the database exists
'and can be referenced using the variable Conn
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
Set rs_2 = Server.CreateObject(&quot;ADODB.Recordset&quot;)

temp_sql = &quot;SELECT * FROM Table_1&quot;
rs.Open temp_sql, Conn, 1, 2
varID = rs(&quot;ID&quot;)

temp_sql = &quot;SELECT * FROM Table_2 WHERE ID=&quot; & varID
rs_2.Open temp_sql, Conn, 1, 2

%>

 

[logicalunatic]

Hrm,

Also, you may have to close the first Recordset before you can open the other. I've never tried opening two Recordsets at the same time and it might produce an error. To close the Recordset type...

rs.Close

between the varID = rs("ID&quot line and the next temp_sql line.

 

[simonchristieis]

There wont be any problem opening multiple recordsets as long as they have different names, but dont close the first one or you wont be able to loop through it.

 

[Tarwn]

The easiest way (note I didn't say most efficient) would be to loop through your first recordset building a list of the ID's, then use the In statement from your first post with that string as the list of possible ID's:

Dim id_list
'queue it to the beginning just in case
If Not rs.EOF Then rs.MoveFirst
'loop through adding to the list
Do Until rs.EOF
   id_list = id_list & rs("ID") & "," 
   rs_list.MoveNext
Loop

'trim the final comma
id_list = Left(id_list,len(id_list)-1)

Dim str_sql2
str_sql2 = "SELECT * FROM Table2 WHERE ID in (" & id_list & ")"

Other ways to accomplish this would be to use the GetRows method of the recordset to drop the data into an array and then the Join method to convert the array to a string, or there is the inner select like you had above which I am unsure why you don't like. There are probably several other ways also, there just not going to come to me until after i hit the submit button

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[Jeremy21b]

I think joining the tables with an SQL query is how I would do it.

SELECT Table_1.*, Table_2.* FROM Table_1 INNER JOIN Table_2 ON Table_1.id = Table_2.id WHERE....

You might need to use a different type of JOIN if every id in table 1, doesn't have a matching entry in table 2.

A different approach is to get all the ids from the first recordset and put them into an array. Then cycle through the array of ids and you'd only need one recordset open at once.

 

[Tarwn]

I was operating under the assumption that your first statement was more complex than a simple SELECT * FROM Table, if that was incorrect then I would definately stand behind the Join method,

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[bomayed]

Thank you guys , I used the Join method and it worked.