Get list of linked self referencing rows-CodePudding

I have table EmployeeTransferHistory as below:

pkTransferId	fkOldEmployeeId	fkNewEmployeeId
1	102	103
2	101	102
3	100	101

Representing a history of transfers. I need to get all employee IDs based on only the latest ID.

For example, given 103 I need to get 103, 102, 101, 100. This can be any number of transfers.

Is this possible with a single query? So far the solutions that I've tried haven't come close.

CodePudding user response：

better way is to add a flag showing "Active Status" based on the latest updation date FKNEWEMPLOYEEID

So while updating the FkNewEmployeeId column do update a new FLG with Active status against the latest updated ID in that case you can easily do that with in the same final query.

CodePudding user response：

I managed to solve it with the following rCTE:

WITH EmployeeTransfer_CTE
AS 
(
    SELECT eth1.fkOldEmployeeID, eth1.fkNewEmployeeID 
    FROM EmployeeTransferHistory eth1
    WHERE eth1.fkNewEmployeeID = @EmployeeID
    UNION ALL
    SELECT eth2.fkOldEmployeeID, eth2.fkNewEmployeeID 
    FROM EmployeeTransfer_CTE
    INNER JOIN EmployeeTransferHistory AS eth2
            ON eth2.fkNewEmployeeID = EmployeeTransfer_CTE.fkOldEmployeeID
)
SELECT fkOldEmployeeID, fkNewEmployeeID
FROM EmployeeTransfer_CTE