How to compare each element from one queryresult with another?

[Petal]

Hi

I wonder how I can compare each element from one queryresult with all elements in another in a good way?
My code:

sql = "SELECT journal_id FROM usersjournals,user where usersjournals.user_id=user.user_id and user.user_name='"&Request.Form("user_name&quot&"'"

set objRS =objCommand.execute(sql)

sql = "SELECT * FROM journal"
set objRS2 =objCommand.execute(sql)

' in both tables the journal_id occur
' I want to write "checked" if the journal_id exist in usersjournals, I think it will be something like this:


while not objRS.EOF
while not objRS2.EOF
if objRS("journal_id&quot=objRS("journal_id&quot then
Response.Write "checked"
end if


objRS2.MoveNext
Wend


objRS.MoveNext

Wend


'But this is not good programming. Could I use an array or list instead? How could I do that? Or if I should do it the way I have done, how to I set objrs2 to start over?

JAA

 

[Francis]

How about this

Do while not objRS.EOF
objRS2.FindFirst "journal_id= " & objRS("journal_id&quot
if objRS2.Nomatch then
Response.Write objRS("journal_id&quot & " Missing"
Else
Response.Write objRS("journal_id&quot & " checked"
end if
objRS.MoveNext
Wend


objRS.MoveNext

 

[BarkingFrog]

If I were doing this I would do it using only one SQL statement. Let the SQL statement do the work for you:

sql = "SELECT a.journal_id FROM usersjournals a,user b, journal c where a.user_id=b.user_id and b.user_name='"&Request.Form("user_name&quot&"' and
a.journal_id = c.journal_id"

This selects the journal id in both tables where the "user_name" is found in usersjournals. Then loop through the recordset and for each record set response.write "checked":

set objRS =objCommand.execute(sql)

while not objRS.EOF
Response.Write "checked"
objRS.MoveNext
Wend

 

[hyonkes]

That examples work with only 1 match in the selection from the query, but , how do you make to work with several matches ?.

Example :

I have 4 families, each family have "n" elements (considerer as subfamilies).

I try to do a "Do While Not Obj.EOF" then a second While
"Do While obj2(fieldname) = variable" and make a "Response.Write".

That code , work with the first match, but with the second match it does`nt show me anything.

Some ideas ?

Thanks in advanced.