2

Given the following Table structure:

Id  OldUrl  Target
1    /a      /b
2    /b      /c
3    /c      /d
4    /x      /y

I want to do a select that "follows" the Target Column, example:

If I pass in ID 1 I want to return row 3 because A would resolve to C if you Select the Target and then Selects the next row where OldUrl = Target Like this:

a -> b
b -> c
c -> d

SQL for creating the table:

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OldUrl] [nvarchar](450) NOT NULL,
    [Target] [nvarchar](max) NOT NULL,
    [Wildcard] [bit] NOT NULL,
    [Disabled] [bit] NOT NULL,
    [Permanent] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I've managed to do that by using the code below, but my SQL skills are very limited so I just want to know if there is a better/cleaner way of doing this?

DECLARE
@Id INT = 1,
@Target nvarchar(MAX) = '',
@Result nvarchar(MAX) = ''

WHILE (1=1)
    BEGIN
        SELECT @Result=(SELECT [Target] FROM MyDatabase.MyTable WHERE Id = @Id)
        if(@Result = @Target)
            break
        SELECT @Id = [Id] FROM MyDatabase.MyTable WHERE OldUrl = @Result

        SET @Target = @Result
    END

SELECT * FROM MyDatabase.MyTable WHERE Id = @Id

Wanted output when passing in Id = 1

3   /c  /d
0

2 Answers 2

3

A recursive CTE will find that for you. By adding in the desc and TOP 1 it will look for the longest possible path. If there were two paths it could follow it will pick one based on the ORDER statement

;WITH follow(idstart, idend, oldurl, target, depth, path) as 
(
    SELECT S.id idstart, S.id idend, S.oldurl, S.target, 1 AS depth, cast(S.oldurl as varchar) AS path
    FROM mytable as S
    UNION ALL
    SELECT S2.id idstart, p.idend idend, S2.oldurl, p.target, p.depth + 1 AS depth, cast(RTRIM(p.path) + '->' + S2.oldurl as varchar) AS path
    FROM follow AS p JOIN mytable as S2 on S2.target = p.oldurl
    WHERE p.target is not null
)
SELECT TOP 1 * FROM follow
WHERE idstart=1
ORDER BY depth desc;
0
2

If your volume/ hardware/ update frequency allowed I would be tempted to pre-calculate this. It will require another column let's call this UltimateTarget. As a new row is written the corresponding values of UltimateTarget are updated.

The first row arrives and is written to the table:

Id  OldUrl  Target  UltimateTarget
1    /a      /b      /b

Then the second row arrives. It is written and any existing row where UltimateTarget = OldUrl is updated. After the write the table looks like:

Id  OldUrl  Target  UltimateTarget
1    /a      /b      /c
2    /b      /c      /c

With all data rows processed the table is:

Id  OldUrl  Target  UltimateTarget
1    /a      /b      /d
2    /b      /c      /d
3    /c      /d      /d
4    /x      /y      /y

Deletes could be trickier, depending on how the chains interact. If they are all independent (A->B->C and X->Y->Z) then it is easy - simply remove the row and close up the pointers. If the chains merge (A->B->C->P and X->Y->Z->P) it gets more complicated. In extremis, they can be re-calculated from scratch, of course.

Without pre-calculation a lazy evaluation may be in order. Once a path has been enumerated, it can be stored against the starting point (and all intermediate nodes) so the expensive path walk need not be performed again. Whether or not this is cost-effective will depend on the read/ write ratio.

I will note in passing that this is textbook material for graph processing. If a move to another tool were possible, or when SQL Server's graph processing matures to a sufficient level, I would recommend considering that approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.