By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH your_table([Current],History,Result)
As
(
SELECT 5,6,'black' UNION ALL
SELECT 6,4,'White' UNION ALL
SELECT 4,1,'Black'
)
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)
| Current | History | Current | Result |
|---|---|---|---|
| 5 | 6 | 4 | Black |