0
votes

I have been searching on the net for an answer to a SQL related question. I hope anyone can help me out.

Table1

Item
ABC4

Table 2

Previous-Item New-Item
ABC1 ABC2
ABC1 ABC3
ABC2 ABC3
ABC3 ABC4

I want to join Table 1 (Item) to Table 2 (New-Item) and get all the previous items (back from ABC4 to ABC1. Combinations are possible in the data. Sometimes an item can only be backtracked from 4 to 1 and sometimes directly. This theoretically could go to infinite. So I am looking for a situation how I can make a loop in a join.

My query right now is:

SELECT 
T1.Item, 
T3.Previous-Item 
FROM Table1 T1 (NOLOCK)
LEFT JOIN Table2 T2 (NOLOCK) ON T1.Item = T2.Previous-Item
LEFT LOOP JOIN Table2 T3(NOLOCK) ON T2.Item = T3.Previous-Item

UPDATE:

The expected result would be:

Item Previous-Item
ABC4 ABC3
ABC4 ABC2
ABC4 ABC1
Can you provide some data samples and their expected result ?. I guess you want a join with a recursive CTE. sqlservertutorial.net/sql-server-basics/…Marc Guillot
Thanks for the quick response. I have updated the questionSQLMark
Is there a reason you're spamming the NOLOCK hint? Why aren't you changing the isolation level when you use it against every table?Larnu