asked on
What is the best way to see if a value exists in one table but not in another in sql server. Here is my problem: I want to make sure that all finishers that are in my local db (as designated by their bib number) are also in the results online. Here is something that works, but is very cumbersome. Looking to clean this up. (Notice that one connection is designated by conn and the other by srvr_conn)
Thanks in advance!
i = 0
ReDim LocalBibs(0)
sql = "SELECT Bib FROM IndRslts WHERE RaceSrvrID = " & lRaceID & " AND Place > 0 AND FnlScnds > 0 ORDER BY Bib"
Set rs = conn.Execute(sql)
Do While Not rs.EOF
LocalBibs(i) = rs(0).Value
i = i + 1
ReDim Preserve LocalBibs(i)
rs.MoveNext
Loop
Set rs = Nothing
For i = 0 To UBound(LocalBibs) - 1
sql = "SELECT Bib FROM IndRslts WHERE RaceSrvrID = " & lRaceID & " AND Place = 0 AND FnlScnds = 0 AND Bib = " & LocalBibs(i)
Set rs = srvr_conn.Execute(sql)
If Not rs.EOF And Not rs.BOF Then
lstMissingBibs.AddItem rs(0).Value
End If
Set rs = Nothing
Next i