SQL Query required using CTE

Question

Can anybody help me in writing an sql query which is defined below: I'm having two table Itemtype and Items

Itemtype
-------------------
ItemTypeID ItemParent TypeName
-------------------------------
1           0          XXX
2           1          YYY
3           1          ZZZ
4           0          SSS
5           4          GGG

Items
--------------------
ItemID ItemTypeID ItemCost
----------------------------------
1        1         5000
2        2        1000
3        4        250
4        3        2000
5        5        400

Output
---------------------------
ItemtypeName ItemCost
------------------------------
XXX            8000 (1000+5000+2000)
SSS            650 (250+400)

These are the two tables:I joined both table using itemtypeid. Now i need to display Items with ItemParentId = 0 and calculate the Itemcost of Items with itemtypeId and ItemparentTypeId having value same as that of ItemTypeID.

I wrote a query using CTE,but its not displaying ItemTypeName.

WITH it_cte AS ( select itemtypeid from ItemType WHERE
ItemType.ItemParentType IS NULL UNION ALL select i.ItemTypeid from
ItemType i  INNER JOIN it_cte icte ON icte.itemtypeid = i.itemtypeid )
select ItemParentType,SUM(Items.ItemCost) as itemcost from ItemType 
left join Items on ItemType.ItemTypeID = Items.ItemTypeID  or
ItemType.ItemParentType= Items.ItemTypeID  group by
ItemType.ItemParentType

Can anybody help?

Thanks Jamuna

Answer 1

Following statement

• uses a CTE to retrieve a list of each ItemTypeID with its root ItemTypeID
• joins with ItemType to get the TypeName
• joins with Items to get the ItemCost
• groups on TypeName to get the sum of the ItemCost

SQL Statement

;WITH q AS (
  SELECT  ItemTypeID , Root = ItemTypeID
  FROM    ItemType
  WHERE   ItemParent = 0
  UNION ALL
  SELECT  t.ItemTypeID, q.Root
  FROM    q
          INNER JOIN ItemType t ON t.ItemParent = q.ItemTypeID
) 
SELECT  it.TypeName, SUM(i.ItemCost)
FROM    q
        INNER JOIN ItemType it ON it.ItemTypeID = q.Root
        INNER JOIN Items i ON i.ItemTypeID = q.ItemTypeID
GROUP BY
        it.TypeName        

Test script

;WITH ItemType (ItemTypeID, ItemParent, TypeName) AS (
  SELECT 1, 0, 'XXX' UNION ALL
  SELECT 2, 1, 'yyy' UNION ALL
  SELECT 3, 1, 'ZZZ' UNION ALL
  SELECT 4, 0, 'SSS' UNION ALL
  SELECT 5, 4, 'GGG'
)
, Items (ItemID, ItemTypeID, ItemCost) AS (
  SELECT 1, 1, 5000 UNION ALL
  SELECT 2, 2, 1000 UNION ALL
  SELECT 3, 4, 250 UNION ALL
  SELECT 4, 3, 2000 UNION ALL
  SELECT 5, 5, 400
)
, q AS (
  SELECT  ItemTypeID , Root = ItemTypeID
  FROM    ItemType
  WHERE   ItemParent = 0
  UNION ALL
  SELECT  t.ItemTypeID, q.Root
  FROM    q
          INNER JOIN ItemType t ON t.ItemParent = q.ItemTypeID
) 
SELECT  it.TypeName, SUM(i.ItemCost)
FROM    q
        INNER JOIN ItemType it ON it.ItemTypeID = q.Root
        INNER JOIN Items i ON i.ItemTypeID = q.ItemTypeID
GROUP BY
        it.TypeName