Want a query for below output (Network Steve Forum)

Want a query for below output

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

July 22nd, 2015 6:35am

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

Free Windows Admin Tool Kit Click here and download it now

July 22nd, 2015 6:57am

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

July 22nd, 2015 7:50pm

This topic is archived. No further replies will be accepted.