Query to List all hierarchical parents and siblings and their childrens, but not list own childrens

Question

I've a basic SQL table with a simple heirarchial connection between each rows. That is there is a ParentID for Every rows and using that its connecting 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

Hope the structure is clear

But my requirement of listng these is a little weird. That is when we pass an AccountID, it should list all its parents and siblings and siblings childrens. But it never list any child of that AccountID to any level. I can explain that in little more detail with a picture. Sorry for the clarity of the picture.. mine is an old phone cam..

When we pass the AccountID 4, it should list all Parents and its siblings, but it should not list 4,6,7,8,9,10. That means that account and any of it childrens should be avoid in the result (Based on the picture tree elements). Hope the explanation is clear.

Answer 1

If I've got it right and you need to output whole table except 4 and all of it's descendants then 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 to the question in the comment:

So it will not traverse to the top. It only traverse to specified account. For example if I pass 4 as first parameter and 2 as second parameter, the result should be these values 2,5,11,12

You should start from the ID=2 and travel to the bottom exclude ID=4 so you cut 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