Finding associated records using two references

Question

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 don't understand your references. I can see how 01 relates to 02, but not to 03 or 04. Similarly, how does 05 relate to 07? — Hand-E-Food, Aug 16 '11 at 06:12
@Hand-E-Food it's chaining. Ref2 of 1 -> Ref 1 of 2. Ref 2 of 2 -> Ref 1 of 3... — Sam DeHaan, Aug 16 '11 at 15:15
I thought that may be the case, but 02.Ref2 was different 03.Ref1 before the question was edited. — Hand-E-Food, Aug 16 '11 at 22:48

score 0 · Answer 1 · edited May 23 '17 at 09:59

0

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.

edited May 23 '17 at 09:59

Community

1
1

answered Aug 16 '11 at 15:20

Sam DeHaan

10,246
2
40
48

How about stored procedures? Could a recursive procedure be written? – Hand-E-Food Aug 16 '11 at 22:49
Recursive queries have been supported in Microsoft Access since the beginning. You can chain/recurse up to 256 queries. You just cannot do it in one huge SQL Statement. – AMissico Aug 16 '11 at 23:18

Hand-E-Food · Answer 2 · 2011-08-16T23:13:29.607

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.

Finding associated records using two references

2 Answers2