One SQL to test multiple records

[Ivor Somerset]

Hi,

I've an Access DB table ("Groups") where data are as follow:

Id Group Rank Item
1 1 1 7364
2 1 2 283
3 1 3 34888
4 2 1 277
5 2 2 8233
(...)

Each record belongs to a group in which it has a rank.

I have an ASP script that writes new groups in the table, but before
writing anything, I'd need to check whether a similar group already
exists, and I think it can be done with a single sql statement. (NB:A
group is similar to another if it has the same Item value at the same Rank.)
The group I want to check is stored in a dictionary object (where the
key stands for the rank).
My basic idea is to iterate through the items to build an sql statement
with nested SELECTs:

sql = "Groups"
For Each a In Dict.Keys
sql = "SELECT * FROM (" & sql1 & ") WHERE Item=" & Dict.Item(a) & " AND
Rank=" & a
Next
sql1 = sql1 & ";"
RS1.Open sql1,Conn

But... of course it doesn't work because the first SELECT matches a
definite record from which the second iteration matches nothing. What I
should have is a set of all groups where the Item at Rank 1 is equal to
the given Item, and so on.

So I guess there should be some (self-)JOIN in the sql statement, but so
far all my attemps have failed.

Any idea? Thanks a lot in advance.

Ivor

 

[Mike Brind]

Hi,

I've an Access DB table ("Groups") where data are as follow:

Id Group Rank Item
1 1 1 7364
2 1 2 283
3 1 3 34888
4 2 1 277
5 2 2 8233
(...)

Each record belongs to a group in which it has a rank.

I have an ASP script that writes new groups in the table, but before
writing anything, I'd need to check whether a similar group already
exists, and I think it can be done with a single sql statement. (NB:A
group is similar to another if it has the same Item value at the same
Rank.)
The group I want to check is stored in a dictionary object (where the key
stands for the rank).
My basic idea is to iterate through the items to build an sql statement
with nested SELECTs:

sql = "Groups"
For Each a In Dict.Keys
sql = "SELECT * FROM (" & sql1 & ") WHERE Item=" & Dict.Item(a) & " AND
Rank=" & a
Next
sql1 = sql1 & ";"
RS1.Open sql1,Conn

But... of course it doesn't work because the first SELECT matches a
definite record from which the second iteration matches nothing. What I
should have is a set of all groups where the Item at Rank 1 is equal to
the given Item, and so on.

So I guess there should be some (self-)JOIN in the sql statement, but so
far all my attemps have failed.

Any idea? Thanks a lot in advance.

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?

 

[Ivor Somerset]

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