Sql-server – Find related names in the table

sql server

I have a table with 2 columns, name and related_name.

CREATE TABLE _test(
[name] NVARCHAR(50),
related_name NVARCHAR(50)
)
GO

INSERT INTO dbo._test
([name], related_name)
VALUES

('a', 'b'),
('b', 'c'),
('c', 'd'),
('e', 'f'),
('f', 'g')

I want to write a UDF, which will get a name and will return all the related names. If a is related with b and b is related with c this means that a is related with c.

So the expected result for parameter 'a', 'b', 'c', 'd' must be the same.

select * from my_func('a')

The result must be

result
------
a
b
c
d

And the result for 

select * from my_func('e')

must be

result
------
e
f
g

Best Answer

You can use a recursive CTE for this, provided you don't have more than 100 relations (since you can't add MAXRECURSION inside the function, so you'd have to apply it on any query that references the function - see an example here).

The first step is to find all the parent-less parents, then use that as an anchor for recursion.

CREATE FUNCTION dbo.my_func(@input nvarchar(50))
RETURNS TABLE 
WITH SCHEMABINDING
AS
  RETURN
  (
    WITH Parents(name) AS
    (
      -- find parent-less parents:
      SELECT name FROM dbo._test
      EXCEPT 
      SELECT related_name FROM dbo._test
    ),
    children AS
    (
      -- use parents as anchor
      SELECT orig = name, name FROM Parents
      UNION ALL
      SELECT c.orig, related_name 
        FROM dbo._test AS t
        INNER JOIN children AS c
        ON c.name = t.name
    )
    SELECT name 
    FROM children AS o
    -- now filter only to a single, common anchor
    WHERE EXISTS
    (
      SELECT 1 FROM children WHERE name = @input AND orig = o.orig
    )
  );

Then you can query:

SELECT name FROM dbo.my_func(N'b');

Results:

name
----
a
b
c
d

Of course it is easy to break this with a circular reference. Don't do that.

Related Question