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

Table1

Table 2

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:

CodePudding user response：

A recursive CTE can do what your LOOP JOIN intends :

CREATE TABLE Table1 ( item varchar(20) )
CREATE TABLE Table2 ( previous_item varchar(20), new_item varchar(20) )

INSERT INTO Table1 (item) 
            values ('ABC4')
INSERT INTO Table2 (previous_item, new_item)
            values ('ABC1', 'ABC2'),
                   ('ABC1', 'ABC3'),
                   ('ABC2', 'ABC3'),
                   ('ABC3', 'ABC4')

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

WITH resursive_cte as (
  select item, item as previous_item, cast(null as varchar(20)) as new_item
  from Table1 -- Anchor (initial records)
  union all
  select recursive_cte.item, Table2.previous_item, Table2.new_item
  from resursive_cte -- Recursive records
       inner join Table2 on Table2.new_item = recursive_cte.previous_item
)          
select distinct item, previous_item
from resursive_cte
where new_item is not null

You can see it working here : db<>fiddle

PS: I have included a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; command, as Larnu has recommended, so you don't need to use the NOLOCK hint manually on every table if you want to get dirty reads.