Retrieving All Parent Child Elements Using MSSQL Query

Question

My database has the following data:

Parent Child 101 102 101 103 101 104 101 105 101 106 

My parameter is 106. And using the parameter, I want to get all the other children under the parent element, which is 101. I tried to use the recursive method, but it did not work, given the following data. Is there any other way to formulate a request?

Answer 1

Assuming you want to get siblings @p0 values, you can use a simple self-join:

 SELECT p.Child FROM Table1 c INNER JOIN Table1 p ON c.Parent = p.Parent WHERE c.Child = @p0 AND p.Child <> @p0 

The offer does not equally guarantee that you will get siblings, not including the value you were looking for. Remove it as needed.

SQL script example

---

Since you mention recursion, perhaps you need the whole tree to start with the parent of the value @p0 . In this case, you can use a recursive CTE:

 WITH parent AS ( SELECT Parent FROM Table1 WHERE Child = @p0 ), tree AS ( SELECT x.Parent, x.Child FROM Table1 x INNER JOIN parent ON x.Parent = parent.Parent UNION ALL SELECT y.Parent, y.Child FROM Table1 y INNER JOIN tree t ON y.Parent = t.Child ) SELECT Parent, Child FROM tree 

Sample SQL script using your data and with additional data to demonstrate a recursive CTE

Answer 2

SQL Authority has a blog with a very good explanation of how to perform a Hierarchical query using a recursive CTE.

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

Hi

Answer 3

 select child from my_table T1 where exists (select 1 from my_table T2 where child = @parameter and T1.parent = T2.parent) 

Answer 4

 WITH parent AS ( SELECT Parent FROM Table1 WHERE Child = @p0 ), tree AS ( SELECT x.Parent, x.Child FROM Table1 x INNER JOIN parent ON x.Parent = parent.Parent UNION ALL SELECT y.Parent, y.Child FROM Table1 y INNER JOIN tree t ON y.Parent = t.Child ) SELECT Parent, Child FROM tree