Rowid (primaryKey)	Accountid	DRAmount	CRAmount	Parentid (foreignKey)
1	501	0	1000	0
2	601	1000	0	1
3	401	2000	0	0
4	501	0	2000	3
5	701	0	500	0
6	801	500	0	5
I want a query to get the below output from above table for a specific account id like for 501 I want second row as child and for 3 row as parent
2	601	1000	0	1
3	401	2000	0	0
or
Rowid (primaryKey)	Accountid	DRAmount	CRAmount	Parentid (foreignKey)
1	501	0	1000	0
2	601	1000	0	1
3	401	2000	0	0
4	501	0	2000	3
Please help

this?

DECLARE @AcctID int = 501

;With Children
AS
(
SELECT *
FROM Table
WHERE AccountId = @AcctID
UNION ALL
SELECT t.*
FROM Table t
INNER JOIN Children c
ON t.ParentId = c.Rowid
),
Parent
AS
(
SELECT *
FROM Table
WHERE AccountId = @AcctID
UNION ALL
SELECT t.*
FROM Table t
INNER JOIN Parent p
ON p.ParentId = t.Rowid
)

SELECT *
FROM Parent
UNION
SELECT *
FROM Children

Try

-- code #1
-- Output 1
declare @AccountID int;
set @AccountID= 501;

SELECT RowID, AccountID, DRAmount, CRAmount
  from table
  where ParentID in (SELECT RowID
                       from table 
                       where AccountID = @AccountID
                             and ParentID = 0)
union all
SELECT RowID, AccountID, DRAmount, CRAmount
  from table
  where RowID in (SELECT ParentID
                       from table 
                       where AccountID = @AccountID
                             and ParentID <> 0);
go

-- Output 2
declare @AccountID int;
set @AccountID= 501;

SELECT RowID, AccountID, DRAmount, CRAmount
  from table
  where AccountID = @AccountID
union all
SELECT RowID, AccountID, DRAmount, CRAmount
  from table
  where ParentID in (SELECT RowID
                       from table 
                       where AccountID = @AccountID)
union all
SELECT RowID, AccountID, DRAmount, CRAmount
  from Lancamento
  where RowID in (SELECT ParentID
                       from table 
                       where AccountID = @AccountID)
order by RowID;
go		

Hi Reenagwb,

If what you'd like is all the parent,grandparent,children,grandchildren and so on, Visakh16's solution with a recursive CTE would be the standard solution.

Just in case you just need the direct parent and children. You may follow the below sample.

DECLARE @T TABLE
(
RowID INT,
AccountID INT,
DRAmount INT,
CRAmount INT,
Parentid INT
)

INSERT INTO @T
VALUES
(1,	501, 0, 1000, 0),
(2,	601, 1000, 0, 1),
(3,	401, 2000, 0, 0),
(4,	501, 0, 2000, 3),
(5,	701, 0, 500, 0),
(6,	801, 500, 0, 5);
 
SELECT * FROM @T T
WHERE EXISTS(SELECT 1 FROM @T WHERE AccountID=501 AND (RowID=t.Parentid OR Parentid=t.RowID))

If you have any question, feel free to le