SQL History Traversal Table

Let’s say I have a table t

Current  History  Result
-------------------------
  5        6      black
  6        4      White
  4        1      Black

With a number of 5, how do I traverse back in history 2 times to derive the result to be black or white?

5 -> 6 -> 4 -> black

Assuming there are other rows of data in the table as well.

I have tried:

select *
from t.result
where t.current in t.history 
  and t.current in t.history where t.current in ('5')

Answer

If the requirement is upto 2 level, you can try this below logic-

DEMO HERE

SELECT A.[Current],A.History,C.[Current],C.Result 
FROM your_table A
INNER JOIN your_table B ON A.History = B.[Current]
INNER JOIN your_table C ON B.History = C.[Current]
WHERE  A.[Current] IN (5)