SQL query into query into query?

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

Well what if you took a different approach. You have a table that lists the stock item but has a code but also a fixed never changing auto incrementing style ID. Your typical ID primary key. Then in another table, each time the item changes, you add one row with the new item code and pointing to the same ID.

To use your example…

1 | My test stock item

Now for a history table…

1 | 1001 | 1
2 | 1001A | 1
3 | 1001B | 1

The last column in this table is pointing at the ID of the product in table A. Notice here that we have three records where 1001, 1001A and 1001B all refer to product with the ID 1. You can search this table for any of those part numbers and link directly to the product with the ID of 1.

If you want to know the last one, you can either take the row with the highest ID in table B or you could also attach a date to each record in Table B and just get the latest date by sorting that column.

Would this be something that works for you?

1 Like

Yes, that does seem like a good idea. I think I got so far down wondering whether I could have some sort of recursive query, or doing it the way I do it on the original system, that I didn’t think of doing it this way, which is by far simpler. There’s no requirement for us to keep a chain of the sequence of part number changes, and I could just add a datetime with a default if I needed to know the order.

Thanks, that’ll do it.

1 Like

I am glad that will work. If you add that date time column, in theory you could also build the chain also. Put the dates in order and suddenly you have a history from the first time the item was created to the last time it was changed and all the changes in between.


This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.