One SQL to test multiple records

Discussion in 'ASP General' started by Ivor Somerset, Dec 7, 2006.

  1. 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
    Ivor Somerset, Dec 7, 2006
    #1
    1. Advertising

  2. Ivor Somerset

    Mike Brind Guest

    "Ivor Somerset" <> wrote in message
    news:4577d202$0$19718$...
    > 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?

    --
    Mike Brind
    Mike Brind, Dec 7, 2006
    #2
    1. Advertising

  3. 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
    >
    >
    Ivor Somerset, Dec 8, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Geoff Winsor
    Replies:
    3
    Views:
    6,698
    Ryan Stewart
    Jan 29, 2005
  2. Luke Airig
    Replies:
    0
    Views:
    785
    Luke Airig
    Dec 31, 2003
  3. Pim75
    Replies:
    7
    Views:
    360
    Hans Kesting
    Mar 14, 2007
  4. Dan

    Delete records or update records

    Dan, May 10, 2004, in forum: ASP General
    Replies:
    1
    Views:
    460
    Ray at
    May 10, 2004
  5. Replies:
    3
    Views:
    653
    Anthony Jones
    Nov 2, 2006
Loading...

Share This Page