Geek Answers Handbook

SQL query required by CTE

Can someone help me write the sql query that is defined below: I have two tables 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 two tables: I joined both tables using itemtypeid. Now I need to display the items with ItemParentId = 0 and also calculate the Itemcost of the items with itemtypeId and ItemparentTypeId having a value, the same as ItemTypeID.

I wrote a query using CTE, but did not display 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

Does anyone help?

Thank you Yamuna

+4
source share
1 answer

The following statement

  • uses CTE to retrieve a list from each ItemTypeID with its root ItemTypeID
  • connects to ItemType to get TypeName
  • connects to Items to get ItemCost
  • groups on TypeName to get the amount of 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
+3
source

More articles:


All Articles