...

View Full Version : record set within another for comparison



havey
05-03-2006, 08:02 PM
Hi, I'm trying to accomplish the following outside of the sql statment, i have a sql statement returning a record set, one returned value being:
objRS("MemberID")
For each instance of this value (which there is Always a value)
I need to check another table for the same MemberID and if it exists then display a 1, if not then 0.

This is what i have so far that i'm trying to get working... any suggestions

For Each MemberID in objRS("MemberID")

strSql2 = "SELECT SurveyDisqualifiedMembers.MemberID FROM SurveyDisqualifiedMembers WHERE SurveyDisqualifiedMembers.MemberID = " & objRS("MemberID")

Set objRS2 = gobjConn.Execute(strSql2)

If objRS2.EOF Then
response.write("0")
Else
response.write("1")
End If

Next

mehere
05-03-2006, 08:06 PM
try this:


do while not objRS.EOF
strSql2 = "SELECT SurveyDisqualifiedMembers.MemberID FROM SurveyDisqualifiedMembers WHERE SurveyDisqualifiedMembers.MemberID = " & objRS("MemberID")
Set objRS2 = gobjConn.Execute(strSql2)
If objRS2.EOF Then
response.write("0")
Else
response.write("1")
End If
objRS.MoveNext
Loop

Spudhead
05-05-2006, 12:07 PM
My SQL isn't great, but it should be possible to do this with just one query - which obviously reduces the load on your database*. And involves less typing :)



sSQL = "SELECT DISTINCT Members.MemberID, SurveyDisqualifiedMembers.MemberID AS DisqMember FROM Members LEFT JOIN SurveyDisqualifiedMembers ON SurveyDisqualifiedMembers.MemberID = Members.MemberID"

Set objRS = gobjConn.Execute(sSQL)



If I'm right, that should return a recordset of two columns; the first is a list of everybody's Member ID, the second... well, it'll be their Member ID if they're in SurveyDisqualifiedMembers, or NULL if they're not. Depending on what database you're using, there are various ways of making it return something a bit more useful than NULL, or you can just deal with it in the ASP.


* In any case, looping through recordsets is Bad. It chews up processor resources. Even if you do stick to looping through one result set, firing off SQL queries each time, have a look into using objRS.getRows() to get your initial data into a more manageable array.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum