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 |
NOLOCK
hint? Why aren't you changing the isolation level when you use it against every table? – Larnu