sql - How to select recursively from query results? -

How can query results be reused in the most efficient way?

I have two tables: items and relationships. Items may be items alone or it may be the child of any other item. Relations relationships maintained in the table. The item is typically identified by depNo, itemNo columns, sample dataset is here:

  create table item (Item ID int identity (1,1) zero, not inypti , Item no Intel no blank, name varchar (50), square int not zero, - 0 - unknown class, 1 - lack of hair item pk_depNo_item no primary key (depNo, itemNo); Creating table relation (not weak relId integer identification (1,1), pDepNo not empty int, pItemNo not empty int, cDepNo null int, cItemNo null int, obstruction pk_parent_child primary key (pDepNo, pItemNo, cDepNo, cItemNo)); Enter the values ​​of objects (1, 1, 'M1CItem1', 1); Insert item values ​​(1, 2, 'M1CItem2', 1); Enter the values ​​of objects (1, 3, 'M1c ETM3', 1); Enter item values ​​(2, 1, 'master1', 0); Insert item values ​​(2, 2, 'LITEM1', 0); Enter the values ​​of items (2, 3, 'LITEM2', 0); Insert item values ​​(2, 4, 'LITEM3', 0); Enter item values ​​(2, 5, 'master 2', 0); Insert item values ​​(2, 6, 'MCCITM1', 1); Insert item values ​​(2, 7, 'MCCITM1', 1); Enter into relationship values ​​(2, 1, 1, 1); Enter into relationship values ​​(2, 1, 1, 2); Enter the relationship values ​​(2, 1, 1, 3); Insert the relationship values ​​(2, 5, 2, 6); Insert the relationship values ​​(2, 5, 2, 7);   
 If the item is a child, then the following questions select all the items that satisfy the criteria of the query or its parents: qRes with  
  Select any result where the item is not a child, select the name of the depno, itemNo, name, where square squares from class qRes, pDepNo, pItemNo externals from objects where '% item 1 '), Such as name depNo = cDepNo and itemNo = cItemNo left to join in relationships & lt; & Gt; 1 Association - Select all the parents of the children. BDPNO, Bitimono, B.A.N.A., B. Select qRes from class. A PDNNO = BDPNO and A PITIM NO = BITIM NO;   
 The query returned will return:  
  depNo item Any name Class 2 1 Master 1 2 2 LITEM1 0 2 5 Master2 0   
 Are there any better ways to deal with this kind of problem?   
 
 
 If you have only one level repetition, then your approach is okay, if you have several levels in the hierarchy You might want to consider using a recurring approach.  
 For example, if I change one of my relationships:  
  set the update relationship pItemNo = 6 where cItemNo = 7   
 This is again a child of  {DepNo: 2, Item No. 7, Name: MCCITIM1}  {DepNo: 2, ItemNo: 6, Name: M2CItem1} < / Code>, which is the child of  in return {DepNo: 2, Item no. 5, Name: Master2}   
 The following are both  M2CItem1  and  Master2    ; In the form of CTE (select depNo, itemNo, name, square, pDepNo, pItemNo, 1, [recursionLevel], item name where name like DepNo = cDepNo and itemNo = cItemNo join the relationship '% 1 All Union Unions I include the internal .depNo, i.itemNo, i.name, i.class, r.pDepNo, r.pItemNo, RecursionLevel + 1 CTE, i.pDepNo = r.cDepNo and i.pItemNo = r on the relation r. CItemNo) Add item to COALESCE from CATE C Inner (c.pDepNo, c.DepNo) = i.DepNo and Coal (c.pItemNo, c.ItemNo No) Choose DISTINCT c.DepNo, c.ItemNo, i.Name, i .Class = I.ItemNo   
 However if you can only use you Want to return the top most parents:  
 ; With CTE (select depno, itemNo, name, class, pDepNo, pItemNo, 1 [recursionLevel] Skip items from Join on Relationships on DepNo = CDepNo and ItemNo = cItem None Names like '% Item1' Unius INDEEp, i.itemNo, i.name, i.class, r.pDepNo, not r.pItem, join recursionLevel + 1 to CTE and Inner Relationships but i.pDepNo = r.cDepNo and i.pItemNo = r CItemNo), as CTE2 (select c.DepNo, c.ItemNo, i.Name, i.Class, RecursionLevel, More than Max (RecursionLevel). By (c. DepNo, c.ItemNo) [MaxRecursionLevel] CTE to C Insider items join COALESCE (c.pDepNo, c.DepNo) = i.DepNo And COALESCE (c.pItemNo, c.ItemNo) = i.ItemNo) on DepNo, itemNo, name, class from CTE2, where recurrence level = maximum vacancies   
 it only  {DepNo: 2, ItemNo: 5, Name: Master2}  {DepNo: 2, ItemNo: 7, Name: M2CItem1}  for the line because it is the parent's parent.  
  
 As a side, I think should you reconsider your schema? If you are not making items and DepNo is your combined identity card for a combined primary key? You should probably choose one or the other, not both.