Query data on the chain

Question

Hello.

I have a product group. Each product in the group has a unique ID. There is a tree relationship between them. Now I need a query. When I enter the ID of any product, I can query all product IDs in the chain where the product is located.

Source table：

ParentID	ChildID
A3A1	A3B1
A3A1	A3B2
A3A1	A3B3
A3B1	A3C1
A3B1	A3C2
A3B1	A3C3
A3B2	A3C4
A3B3	A3C5
A3B3	A3C6
A3C2	A3D1
A3C2	A3D2
A3C2	A3D3
A3C4	A3D4
A3C5	A3D5
A3C5	A3D6
A3D4	A3E1
A3D5	A3E2
A3D5	A3E3

For example, when I enter 'A3C4', the query should find A3B2, A3A1, A3D4 and A3E1.

Thank you in advance.

Answer 1

Hi @Annie

If I understand correctly, you can try this query.

create table Sourcetable(ParentID varchar(10),ChildID varchar(10));
insert into Sourcetable values('A3A1','A3B1'),('A3A1','A3B2'),
('A3A1','A3B3'),('A3B1','A3C1'),('A3B1','A3C2'),('A3B1','A3C3'),
('A3B2','A3C4'),('A3B3','A3C5'),('A3B3','A3C6'),('A3C2','A3D1'),
('A3C2','A3D2'),('A3C2','A3D3'),('A3C4','A3D4'),('A3C5','A3D5'),
('A3C5','A3D6'),('A3D4','A3E1'),('A3D5','A3E2'),('A3D5','A3E3');

Declare @ID varchar(10) = 'A3C4';--You can enter the ID you want to query here.
;with T1 as(
  select * from Sourcetable where ParentID = @ID 
  union all
  select B.* from T1 as A inner join Sourcetable as B
  on A.ChildID = B.ParentID
),T2 as(
  select * from Sourcetable where ChildID = @ID
  union all
  select D.* from T2 as C inner join Sourcetable as D
  on C.ParentID = D.ChildID)
select ChildID as Result from T1
union
select ParentID from T2;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".