I am writing some code in vb.net and MS SQL Server. One of the tables I need is a stock code lookup. The background is that the stock code may change over time, so the way I handle that in another system is to have a cross-reference file, the key is the old part number which then refers to the new stock code. But, the stock code might change more than one time, so I need to keep looking in the cross reference file until the part number does not exist in there, at which point I (hopefully) have an entry in the main stock file. The point being that the user can enter any of the previous stock codes and it will “cascade” to the correct one no matter whether they enter the first, or the most recent old stock code.
Say we have a code 1001, My test stock item. We change that code to 1001A, so the main stock file now contains an entry for 1001A but not for 1001. The cross-reference contains a link from 1001 to 1001A.
Next, we change the code from 1001A to 1001B, so the main stock file now contains 1001B instead of 1001A. The cross-reference now has two entries, a link from 1001 to 1001A, and a link from 1001A to 1001B. And so on. (*) The user can enter 1001 or 1001A and the system will automatically retrieve 1001B.
Now that I’m trying to replicate this functionality in SQL server and VB, I wonder whether there is something that SQL Server can do to basically return the last link in the “chain”, in this case 1001B. I know I can run multiple queries one after another from VB until I get no rows returned, but does anyone know of some way I can make SQL Server do this simply, given that there’s no limit to how many steps there might be? In practice it’s probably not that many, but it’s not a defined number. I’m new to SQL Server, I can do basic queries but the more complex stuff is out of my range at the moment. I’m struggling to search for anything similar as I’m not quite sure how to describe what I want - “cascading” doesn’t really do it.
(* Because of historic data on the system, there’s a need to maintain a list of older part numbers, so it’s not just a case of referring to parts using a unique non-changing id and then just changing the part number like in a normalised database. If the user placed an order a year ago for 1001, we don’t want it to change to 1001A and then 1001B, we need to still know they bought the older version.)