Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Mar 2003
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    record set within another for comparison

    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

  • #2
    Regular Coder
    Join Date
    Jan 2005
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try this:
    Code:
    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

  • #3
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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

    Code:
    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.
    Last edited by Spudhead; 05-05-2006 at 12:11 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •