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.
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. :-)
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.