Avatar of Bob Schneider
Bob SchneiderFlag for United States of America asked on

Prevent Duplicates in ASP Array

I want to use GetRows() in this query but I want to ensure that no meet included multiple times.  The goal is to speed the query up.  This "works" but is way slow and inefficient.  Thanks in advance!

    Set rs = Server.CreateObject("ADODB.Recordset")
    sql = "SELECT m.MeetsID, m.MeetName, m.MeetDate, m.MeetSite FROM Meets m INNER JOIN MeetTeams mt ON m.MeetsID = mt.MeetsID INNER JOIN Roster r ON "
    sql = sql & "mt.TeamsID = r.TeamsID INNER JOIN IndRslts ir ON ir.MeetsID = mt.MeetsID WHERE ir.RosterID IN (" & sSelectQS & ") AND ir.Place > 0 "
    sql = sql & "AND (m.MeetDate >= '" & dBegDate & "' AND m.MeetDate <= '" & dEndDate & "') AND Sport = '" & sSport & "' ORDER BY m.MeetSite DESC"
    rs.Open sql, conn2, 1, 2
    Do While Not rs.EOF
        If x = 0 Then
            AllMeets(0, x) = rs(0).Value
            AllMeets(1, x) = Replace(rs(1).Value, "''", "'") & " (" & Month(rs(2).Value) & "/" & Year(rs(2).Value) & ")"
            AllMeets(2, x) = rs(2).Value
            AllMeets(3, x) = Replace(rs(3).Value, "''", "'")
            x = x + 1
            ReDim Preserve AllMeets(3, x)
        Else
            For z = 0 To UBound(AllMeets, 2) - 1
                If CLng(rs(0).Value) = CLng(AllMeets(0, z)) Then
                    Exit For
                Else
                    If z = UBound(AllMeets, 2) - 1 Then
                        AllMeets(0, x) = rs(0).Value
                        AllMeets(1, x) = Replace(rs(1).Value, "''", "'") & " (" & Month(rs(2).Value) & "/" & Year(rs(2).Value) & ")"
                        AllMeets(2, x) = rs(2).Value
                        AllMeets(3, x) = Replace(rs(3).Value, "''", "'")
                        x = x + 1
                        ReDim Preserve AllMeets(3, x)
                    End If
                End If
            Next
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

Open in new window

Microsoft SQL ServerASPSQL

Avatar of undefined
Last Comment
Scott Fell

8/22/2022 - Mon
SOLUTION
Pawan Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Bob Schneider

This query culls all Nordic ski meets participated in by a group of skiers over a period of time.  They have not all competed in all meets during this period of time.  I want to get all meet info (m.MeetsID, m.MeetName, m.MeetDate, m.MeetSite) for every meet that at least one of these skiers participated in but I don't want the meet listed multiple times just because multiple skiers skied in the same meet.
SOLUTION
Pawan Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Bob Schneider

Thank you.  What is CTE1 and what do you mean "my final query"?  The actual sql query I am currently using?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23