0

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

2 Answers 2

1

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.

0
0

The query (both ways):

declare @name nvarchar(50) = N'd'
;WITH CTE AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION
select name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE on cte.related_name = t1.name 
),
 CTE2 AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION 
select  name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE2 on t1.related_name = cte2.name 
)

select DISTINCT name  from cte
UNION
SELECT DISTINCT cte.related_name from cte 
UNION
select DISTINCT name  from cte2
UNION
SELECT DISTINCT cte2.related_name from cte2

Result:

 name
e
f
g

As a table valued function:

ALTER function dbo.my_func(@name nvarchar(50))
    returns table
    as

    RETURN
    (WITH CTE AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION
select name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE on cte.related_name = t1.name 
),
 CTE2 AS
(
(select  name ,  related_name from
dbo._test
where name = @name
UNION 
select  name ,  related_name from
dbo._test
where related_name = @name)
UNION ALL 
select t1.name, t1.related_name from dbo._test t1
INNER JOIN CTE2 on t1.related_name = cte2.name 
)

select DISTINCT name  from cte
UNION
SELECT DISTINCT cte.related_name from cte 
UNION
select DISTINCT name  from cte2
UNION
SELECT DISTINCT cte2.related_name from cte2

)

Test

select * from dbo.my_func('b')

Result:

name
a
b
c
d

Test 2

select * from dbo.my_func('e')

Result:

name
e
f
g
5
  • The result for param 'a', 'b', 'c', 'd' must be the same, but for 'b' we get only b, c and d, Dec 12, 2018 at 13:15
  • Hi Artashes, i edited it, it should work both ways now. Dec 12, 2018 at 13:24
  • 1
    Hi, the edited query works incorrect. If we pass 'c' or 'd' in returns incorrect results. Dec 12, 2018 at 13:42
  • I think you read it too literally, and ignored the examples. The OP wanted related names in either direction. In your function both c and d do not work according to the OP's requirements, because a,b,c, and d should all return the same result, and they don't. Dec 12, 2018 at 14:05
  • True, i did read it like that, and i should have read everything. I fixed it now to correspond to the requirements. My bad. Dec 12, 2018 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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