Hello Mike,
Thanks for taking the time to have a look at my obscure problem. It was
probably not well explained and the sql/sql1 typo surely didn't help.
In fact I was on the right track mentioning self-joins and I finally
found the solution.
sql1 = "Groups"
For Each a In Dict.Keys
sql1 = "(SELECT T2.IdGroup,T2.Rank,T2.IdItem FROM (" & sql1 & " AS T1
LEFT JOIN Groups AS T2 ON T1.IdGroup=T2.IdGroup) WHERE T1.IdItem=2712
AND T1.Rank=1)"
Next
sql1 = sql1 = Mid(sql1,2,Len(sql1) - 2) & ";"
RS1.Open sql1,Conn
Response.Write "Matching group in the table? " & Not(RS1.EOF)
Suppose I have a dictionary object containing:
Item("1") = "2712"
Item("2") = "5598"
Each key/value pair stands for the Rank and IdItem fields of my Groups
table in the DB.
Once built, the sql1 statement is:
SELECT T2.IdGroup,T2.Rank,T2.IdItem FROM ((SELECT
T2.IdGroup,T2.Rank,T2.IdItem FROM (Groups AS T1 LEFT JOIN Groups AS T2
ON T1.IdGroup=T2.IdGroup) WHERE T1.IdItem=2712 AND T1.Rank=1) AS T1 LEFT
JOIN Groups AS T2 ON T1.IdGroup=T2.IdGroup) WHERE T1.IdItem=5598 AND
T1.Rank=2;
When I execute it, Not(RS.EOF) tells me if the Groups table has 2
records such as:
IdGroup:[some Id] / IdItem:2712 / Rank:1
AND
IdGroup:[some Id] / IdItem:5598 / Rank:2
[someId] having of course the same value in both records.
(In fact it's not quite over yet, I still have to check whether there's
a third record where IdGroup:[some Id], because it would mean that the
group described by the dictionary object and the group in the DB are not
similar.)
I don't know if my problem is any learer to you now.
I think this is a quick way to compare a "set of records" against a
table, certainly more straightforward than a recursive function.
Ivor
>
What you are trying to achieve is not very clear. On the face of it, it
seems straightforward, but then you introduce "nested selects" which
confuses things.
If what you want to do is iterate over the collection in the dictionery
object, checking to see if each entry has a match in the Access table, then
you have to execute the sql within each iteration.
<%
For Each a In Dict.Keys
sql = "SELECT Id FROM GROUPS WHERE Item=" & Dict.Item(a) & " AND
Rank=" & a
Set rs1 = conn.execute(sql)
If Not rs1.EOF Then 'you have a match
...
Else 'you don't
...
End If
Next
%>
Your current SQL statement - ignoring the fact that you swap from "sql" to
"sqll" will only ever end up containing the values from the final item in
the dictionery object when yo ucome to execute it. You will have
overwritten all the preceding ones without ever having tested them.
Or were you trying to achieve something else?
--
Mike Brind