Revursive SQL

Given a lookup table Mappings such as follows

ID	OldValue	NewValue
1	A	B
2	C	D
3	B	E
4	E	F
5	G	H

CREATE TABLE Mappings (id int , OldValue nvarchar, NewValue nvarchar)

INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (1,'A','B')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (2,'C','D')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (3,'B','E')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (4,'E','F')
INSERT INTO Mappings (id, OldValue, NewValue) VALUES   (5,'G','H')

Given entry A, how do we follow the table all the way to get to the final value of F?

We can obviously do this using a Recursive Function:


CREATE FUNCTION Map(@OldValue nvarchar)
RETURNS nvarchar
AS
BEGIN
DECLARE @RET nvarchar;
SELECT @RET = NewValue FROM Mappings WHERE OldValue = @OldValue
IF (@RET IS NULL) SET @RET = @OldValue
ELSE SET @RET = dbo.Map(@RET)
RETURN @RET
END
GO

SELECT dbo.Map('C') -- returns D
SELECT dbo.Map('D') -- returns D
SELECT dbo.Map('A') -- returns F
SELECT dbo.Map('B') -- returns F


But can we do it just using normal SQL Queries? The answer is yes!

This shows the working


DECLARE @startingPoint NVarchar(max) = 'A';

 
  with working_tree as (
   select [OldValue] , [NewValue]
   from [Mappings]
   where [OldValue] =  @startingPoint-- this is the starting point you want in your recursion
   union all
   select  c.[OldValue], c.[NewValue]
   from [Mappings] c
     join working_tree p on p.[NewValue] = c.[OldValue]  -- this is the recursion
)

select *
from working_tree;

OldValue NewValue
A               B
B               E
E               F


And this is the final form.


  with working_tree as (
   select [OldValue] , [NewValue]
   from [Mappings]
   where [OldValue] =  @startingPoint-- this is the starting point you want in your recursion
   union all
   select  c.[OldValue], c.[NewValue]
   from [Mappings] c
     join working_tree p on p.[NewValue] = c.[OldValue]  -- this is the recursion
)
select t1.NewValue
from working_tree t1
LEFT JOIN [Mappings] t2 ON t2.OldValue = t1.NewValue
where t2.OldValue IS NULL

This returns F, as expected.