问题描述:

I have a table called records and it contains three columns:

ID Ref1 Ref2

-- ---- ----

01 abcd efgh

02 efgh ijkl

03 ijkl qrst

04 qrst ""

05 1234 5678

06 5678 9999

07 9999 8888

The result I am trying to achieve is:

when I select record 01, I would like to see all related records. Records are related through Ref1 and Ref2, therefore the result of selecting record 01 would be records 01 to 04; if I select record 02 I should still see records 01 to 04; if I select record 05 then I would see records 05 to 07 etc.

Constraints:

I use access as the database and asp .net web pages as the 'front end'. If it can't be done using SQL, then VB.net or C# can be used.

网友答案:

I can't speak for VB.net or C#, but I don't believe this is possible in straight SQL unless the max reference chain length is known.

I can almost see a solution using a self-join on ref1 = ref2, but the problem is the recursion. Recursive queries are not supported in access.

One of the (not accepted) answers to the question I linked may have a solution for recursive sql through a combination of SQL and VBA in Access, but I can't speak for it.

网友答案:

I'm sure someone else can improve on this answer.

I'd use .Net code 'cos that's what I do. :-)

Pseudocode:

function GetRelatedRecords(FirstID) as List of Records
    Results = new List of Records
    SoughtRefs = new List of RefNumbers
    UsedRefs = new List of RefNumbers
    NewRecords = SELECT * FROM Table WHERE ID = FirstID
    loop
        for each Record in NewRecords
            if Record not in Results then add Record to Results
            if Record.Ref1 not in UsedRefs or SoughtRefs then add Record.Ref1 to SoughtRefs
            if Record.Ref2 not in UsedRefs or SoughtRefs then add Record.Ref2 to SoughtRefs
        next
        if SoughtRefs is empty then exit loop
        NewRecords = SELECT * FROM Table WHERE Ref1 IN SoughtRefs OR Ref2 IN SoughtRefs
        move all SoughtRefs to UsedRefs
    end loop
    return Results
end function

Basically, takes the ref numbers of each record and searches for ref numbers that haven't been searched for before. By excluding used ref numbers, it cuts down on SQL returning redundant records over and over again. You'll get each record twice at most. You could add OR NOT [ID] IN Results.ID to the SQL query, but I'd be checking the performance to see if it's worth comparing against so many ID numbers.

相关阅读:
Top