A request to view all hierarchical parents and siblings and their children, but not a list of their own children

Question

I have a basic SQL table with a simple hierarchical relationship between each row. That is, there is a ParentID for each row and the use of this connection with another row. Its as follows

AccountID | AccountName | ParentID --------------------------------------- 1 Mathew 0 2 Philip 1 3 John 2 4 Susan 2 5 Anita 1 6 Aimy 1 7 Elsa 3 8 Anna 7 ............................. ................................. 45 Kristoff 8 

We hope the structure is clear.

But my requirement to list them is a little strange. That is, when we transfer the account identifier, it must list all of its parents and siblings and siblings. But it never lists any child of this account identifier at any level. I can explain this in more detail with the image. Sorry for the clarity of the picture .. my old phone.

When we pass AccountID 4 , it should list all the parents and their siblings , but it should not list 4,6,7,8,9,10 . This means that in this case, you should avoid the account and any of its children (based on the elements of the picture tree). Hope the explanation is clear.

Answer 1

If everything works out for me, and you need to display the whole table, except 4, and all its descendants will try this recursive query:

 WITH CT AS ( SELECT * FROM T WHERE AccountID=4 UNION ALL SELECT T.* FROM T JOIN CT ON T.ParentID = CT.AccountId ) SELECT * FROM T WHERE AccountID NOT IN (SELECT AccountID FROM CT) 

SQLFiddle demo

Answering a question in a comment:

Thus, he will not go to the top. It passes only to the specified Account. For example, if I pass 4 as the first parameter and 2 as the second parameter, the result should be these values ​​2,5,11,12

You should start with ID = 2 and move to the bottom, excluding ID = 4, so that you cut the whole subtree after ID = 4:

 WITH CT AS ( SELECT * FROM T WHERE AccountID=2 UNION ALL SELECT T.* FROM T JOIN CT ON T.ParentID = CT.AccountId WHERE T.AccountId<>4 ) SELECT * FROM CT 

Answer 2

Try the following:

 ;with cte as (select accountid,parentid, 0 as level from tbl where parentid = 0 union all select t.accountid,t.parentid,(level+1) from cte c inner join tbl t on c.accountid= t.parentid ) select * from cte where level < (select level from cte where accountid = @accountid) 

When you pass the @accountid parameter, this will return the accountid values ​​of all nodes at the levels down to the parameter level.

If you want to return everything at the same level as the input, except for the input itself, you can change the where clause to;

 where level <=(select level from cte where accountid= @accountid ) and accountid <> @accountid 

In your example, if @accountid = 4, this will return the values ​​1,2,3 (ancestors), as well as 5,13,14 (brothers and sisters).

Answer 3

Does this mean that you are after?

 declare @AccountID int set @AccountID = 4 ;with parents as ( select AccountID, AccountName, ParentID from Account where AccountID = (select ParentID from Account Where AccountID = @AccountID) union all select A.AccountID, A.AccountName, A.ParentID from Account as A join parents as P on P.ParentID = A.AccountID ), children as ( select AccountID, AccountName, ParentID from parents union all select A.AccountID, A.AccountName, A.ParentID from Account as A join children as C on C.AccountID = A.ParentID where A.AccountID <> @AccountID ) select distinct AccountID, AccountName, ParentID from children order by AccountID 

Answer 4

To me it sounds like you want to climb a tree. Therefore, given the test data

 DECLARE @tbl TABLE(AccountID INT,AccountName VARCHAR(100),ParentID INT) INSERT INTO @tbl VALUES (1,'Mathew',0), (2,'Philip',1), (3,'John',2), (4,'Susan',2), (5,'Anita',1), (6,'Aimy',1), (7,'Elsa',3), (8,'Anna',7) 

I would write a query like this:

 DECLARE @AcountID INT=4 ;WITH CTE AS ( SELECT tbl.AccountID, tbl.AccountName, tbl.ParentID FROM @tbl AS tbl WHERE tbl.AccountID=@AcountID UNION ALL SELECT tbl.AccountID, tbl.AccountName, tbl.ParentID FROM @tbl AS tbl JOIN CTE ON CTE.ParentID=tbl.AccountID ) SELECT * FROM CTE WHERE NOT CTE.AccountID=@AcountID 

This will return the result as follows:

 2 Philip 1 1 Mathew 0