Display a parent/child relation within the same table

here is the code

SELECT ID AS ParentItems, 
       Cost AS ParentCost, 
       (SELECT SUM(Cost) FROM YOUR_TABLE AS ChildTable WHERE ChildTable.ItemParentId = ParentTable.ID) AS ChildCost 
FROM YOUR_TABLE AS ParentTable
WHERE ItemParentId = 0

                                              

1:
2:
3:
4:
5:

HI,
try below

select p.itemcode , p.cost as parentCost, c.cost/c.packaging
from tableName p join 
(select * from tableName where parentid is not null) as c 
ON p.id = c.parentId
where p.parentid is null

                                              

1:
2:
3:
4:
5:
6:

sorry missed alias for column name

select p.itemcode , p.cost as parentCost, c.cost/c.packaging AS ChildCost
from tableName p join 
(select * from tableName where parentid is not null) as c 
ON p.id = c.parentId
where p.parentid is null

                                              

1:
2:
3:
4:
5:

sorry wrong aliases. ignore the first 2

select p.itemcode , p.cost as parentCost, p.cost/c.packaging AS ChildCost
from tableName p join 
(select * from tableName where parentid is not null) as c 
ON p.id = c.parentId
where p.parentid is null
 
OR
 
select p.itemcode , p.cost as parentCost, c.cost AS ChildCost
from tableName p join 
(select * from tableName where parentid is not null) as c 
ON p.id = c.parentId
where p.parentid is null

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

DECLARE @ItemTable TABLE(ID INT,ItemCode VARCHAR(255),ItemParentId INT,Cost DECIMAL, Packaging INT)


--                                                ID  ItemCode           ItemParentId     Cost            Packaging
--                                                --  -----------------  ---------------      --------      ---------------
INSERT INTO @ItemTable  VALUES ( 51,'Parent01',                  0,                        24.00,            0 )
INSERT INTO @ItemTable  VALUES ( 50,'Child01',                  51,                        1.00,            24 )
INSERT INTO @ItemTable  VALUES ( 55,'RegularItem1',            0,                        15.95,            0 )
INSERT INTO @ItemTable  VALUES ( 59,'RegularItem2',            0,                        7.50,            0 )
INSERT INTO @ItemTable  VALUES ( 66,'Parent02',                  0,                        65.90,            0 )
INSERT INTO @ItemTable  VALUES ( 75,'Child02',                  66.00,                  1.830,            36 )

SELECT * FROM @ItemTable


SELECT  ParentItemTable.ItemCode,
        SUM(ParentItemTable.Cost),
        SUM(ChildItemTable.Cost)
FROM    @ItemTable AS ParentItemTable
        INNER JOIN @ItemTable AS ChildItemTable ON ParentItemTable.ID = ChildItemTable.ItemParentId
GROUP BY ParentItemTable.ItemCode

here's the full code

drop table #tablename
 
create table #tablename
(id int identity(1,1), parentid int, itemcode varchar(20), cost int, packaging int )
 
insert into #tablename (parentid, itemcode, cost, packaging)
select null, 'Parent01', 12000, 0 union
select 1, 'Child01', 1000, 12 union
select null, 'Parent02', 24000, 0 union
select 2, 'Child02', 2000, 12 union
select null, 'Parent03', 12000, 0
 
select * from #tablename
 
select p.id ,p.itemcode , p.cost as parentCost, c.cost AS ChildCost
from #tableName p join 
(select * from #tableName where parentid is not null) as c 
ON p.id = c.parentId
where p.parentid is null

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Hi MelissaSuciadi, I tried your third script and it didn't work, it return empy.  But when I deleted the last line ("where p.ParentItem is null"), it seems to work.  Can you validate your script :

select p.itemcode , p.cost as parentCost, p.cost/c.packaging AS ChildCost
from tableName p join
(select * from tableName where parentid is not null) as c
ON p.id = c.parentId
-- where p.parentid is null  (deleted, it seems to work)

NOTE: but when I tried your last script, it does work with the last line.  Let me say that the column parentid  is never null (just in case), Iwould this change anything logic in your script?

ImranTaj, I dont have 2 tables, both Parent and Child are in the same table.

I think I got it MelissaSuciadi.  I deleted the last line since this column is never NULL, is "0".

please validate this so i can close the question.

you can try this one

SELECT ItemCode AS ParentItems, 
       Cost AS ParentCost, 
       (SELECT ChildTable.Cost FROM TableName AS ChildTable WHERE ChildTable.ItemParentId = ParentTable.ID) AS ChildCost 
FROM TableName AS ParentTable
WHERE ItemParentId = 0

                                              

1:
2:
3:
4:
5:

ImranTaj's query is also correct, just remove
  SELECT * FROM @ItemTable
line from his post.


or you can have this query also.

DECLARE @ItemTable TABLE(ID INT,ItemCode VARCHAR(255),ItemParentId FLOAT, Cost FLOAT, Packaging INT)
 
INSERT INTO @ItemTable  VALUES ( 51,'Parent01',     0,     24.00, 0 ) 
INSERT INTO @ItemTable  VALUES ( 50,'Child01',      51,    1.00,  24 ) 
INSERT INTO @ItemTable  VALUES ( 55,'RegularItem1', 0,     15.95, 0 ) 
INSERT INTO @ItemTable  VALUES ( 59,'RegularItem2', 0,     7.50,  0 ) 
INSERT INTO @ItemTable  VALUES ( 66,'Parent02',     0,     65.90, 0 ) 
INSERT INTO @ItemTable  VALUES ( 75,'Child02',      66.00, 1.830, 36 ) 
 
SELECT ParentTable.ItemCode AS ParentItems, 
       ParentTable.Cost AS ParentCost, 
       ChildTable.Cost AS ChildCost
FROM @ItemTable AS ParentTable
INNER JOIN @ItemTable AS ChildTable
ON ChildTable.ItemParentId = ParentTable.ID
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

ranned it and it return empty.

Listen, but your your previous script (see below) works fine.  Can I replace the NULL for the 0?

select p.itemcode , p.cost as parentCost, p.cost/c.packaging AS ChildCost
from tableName p join
(select * from tableName where parentid is not null) as c
ON p.id = c.parentId
-- where p.parentid is null  (deleted, it seems to work)

tell me your original table name. i will send you the query.

OR

you can also remove this line
  where p.parentid is null

or replace it with
  where p.parentid = 0

thanx u r great!

I will close this question

I will place a question related to this one, how can I some send u this new question so u can look at it since u helped me with this one?

worked excelent!

Thanx


I placing the followup question so I can place her for your assistance

I'll be closing in just a bit

Please followup on the following question:

Display Parent Items rows and Non-Parent row in SQL script