Recursively read table with a linked list

Question

0.00/5 (No votes)

See more:

I have a table that holds a friends tree;
FID is the friend ID and MId is who invited that friend to join.

I would like to query the table to get all nested friends that the initial trigger to join was done my MID 3 (for example)

Table for example:

FID     MID     Vflag
1   9   t         
2   9   t         
3   9   t         
4   9   f         
5   9   f         
6   9   t         
7   5   t         
8   3   t         
9   3   t         
10  3   t         
11  3   t         
12  4   t         
15  10  t         
16  10  t         
17  10  t         
18  15  t         
19  15  t         
20  15  t

What I have tried:

SQL

WITH Ancestors(InvitedBy, Child) AS

(

	SELECT InvitedBy, FriendID as Child

	FROM PromotionPlan WHERE FriendID IS NOT NULL
	   
	UNION ALL
	   
	SELECT b.InvitedBy, a.FriendID as Child FROM 

	PromotionPlan a, Ancestors b

	WHERE b.Child = a.InvitedBy

)

SELECT a.* FROM PromotionPlan a WHERE InvitedBy IN (SELECT InvitedBy FROM Ancestors WHERE Child=3)

Posted 13-May-20 8:03am

Member 4529316

Updated 14-May-20 1:28am

Maciej Los

v2

Add a Solution

Comments

[no name] 13-May-20 14:07pm

This should help: Find the number of descendant nodes in a SQL query[^]

Maciej Los 14-May-20 1:51am

Sample data does not corresponds to query.

Member 4529316 14-May-20 2:24am

I wrote the column names in short, Fid - FriendID, MID - InvitedBy,vFlag(same)

Maciej Los 14-May-20 3:10am

OK, sorry.

Maciej Los 14-May-20 3:43am

When i look at your data, i'm bit confused... Seems, your data is wrong. Why?
FriendID=3 was invited by 9 and FriendID=9 was invited by 3. So, this causes cte to go into infinite loop:
3->9->9->3->3->9..... and so on...

Member 4529316 14-May-20 7:09am

oops, good catch didn't notice that

Maciej Los 14-May-20 8:52am

;)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2020-05-13T22:34:00

Please, read my comment about infinite loop first.

With small changes in your data...

SQL

DECLARE @PromotionPlan TABLE(FriendID int, InvitedBy int, Vflag VARCHAR(1))

INSERT INTO @PromotionPlan(FriendID, InvitedBy, Vflag)
VALUES(1, NULL, 't'), (2, 1, 't'), (3, 2, 't'),
(4, 9, 'f'), (5, 9, 'f'), (6, 9, 't'),
(7, 5, 't'), (8, 3, 't'), (9, 3, 't'),
(10, 3, 't'), (11, 3, 't'), (12, 4, 't'),
(15, 10, 't'), (16, 10, 't'), (17, 10, 't'),
(18, 15, 't'), (19, 15, 't'), (20, 15, 't')

;WITH Ancestors AS
(
	--initial part
	SELECT 0 AS Distance, FriendID , InvitedBy, CONVERT(varchar(MAX), CAST(FriendID AS VARCHAR(10))+ '<-' + CAST(InvitedBy AS VARCHAR(10))) AS Relation
	FROM @PromotionPlan
	WHERE FriendID =20
	UNION ALL
	SELECT Distance + 1, b.FriendID, b.InvitedBy, Relation + '<-' + CAST(b.InvitedBy AS VARCHAR(10))
	FROM Ancestors a INNER JOIN  @PromotionPlan b ON a.InvitedBy = b.FriendID
	WHERE NOT b.InvitedBy IS NULL
)
SELECT  *
FROM Ancestors

Result:

Distance	FriendID	InvitedBy	Relation
0	20	15	20<-15
1	15	10	20<-15<-10
2	10	3	20<-15<-10<-3
3	3	2	20<-15<-10<-3<-2
4	2	1	20<-15<-10<-3<-2<-1

Member 4529316 · Accepted Answer · 2020-05-14T01:29:00

SQL

-- Change you answer slightly so it will give me the opposite 
-- having friends tree
;WITH Ancestors AS
(
	--initial part
	SELECT 0 AS Distance, InvitedBy, FriendID , CONVERT(varchar(MAX),  CAST(InvitedBy AS VARCHAR(10))+ '<-' + CAST(FriendID AS VARCHAR(10))) AS Relation
	FROM PromotionPlan
	WHERE InvitedBy =3
	UNION ALL
	SELECT Distance + 1,  b.InvitedBy,b.FriendID,Relation + '<-' + CAST(b.FriendID AS VARCHAR(10))
	FROM Ancestors a INNER JOIN  PromotionPlan b ON a.FriendID = b.InvitedBy
	WHERE NOT b.FriendID IS NULL
)
SELECT  *
FROM Ancestors order by Distance

Results:

Distance	InvitedBy	FriendID	Relation
0	3	8	3<-8
0	3	10	3<-10
0	3	11	3<-11
1	11	16	3<-11<-16
1	10	15	3<-10<-15
1	10	17	3<-10<-17
2	15	18	3<-10<-15<-18
2	15	19	3<-10<-15<-19
2	15	20	3<-10<-15<-20
2	16	21	3<-11<-16<-21

Recursively read table with a linked list

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0