I want to organize many-to-many communication in one table. Task. There are parts that can be included in assembly units. Also, assembly units may be included in more complex assembly units, and parts may also be included there.

For example, a screw , a nut , a washer , a base are included in the assembly under the name BASIS a screw , a nut , a cover are included in the assembly panel .

And screw , base , panel are part of the case

the task is to find a hierarchical tree of all components that make up the body and panel.

The label represents the following view.

RelationID, ProductID (part or assembly which is included), ToProductID (which includes)

In the plate the following data

 1 1 5
 2 2 5
 3 3 5
 4 4 5
 5 5 6
 6 6 7
 7 1 7
 8 2 7
 9 3 7

The following code is used to build the hierarchy for item 7.

WITH CTE(ProductID, ToProductID) AS ( SELECT ProductID, ToProductID FROM Relations WHERE ToProductID = 7 UNION ALL SELECT j.ProductID, j.ToProductID FROM CTE AS CCTE INNER JOIN Relations AS j ON CCTE.ProductID = j.ToProductID ) SELECT ProductID, ToProductID FROM CTE AS CTE1

The code prints a normal tree.

 6 7
 1 7
 2 7
 3 7
 5 6
 15
 2 5
 3 5
 4 5

But I just can not understand how it works (in my opinion it should not). As I understand.

The first SELECT call anchors, from where further SELECT ProductID, ToProductID FROM Relations WHERE ToProductID =7 queries SELECT ProductID, ToProductID FROM Relations WHERE ToProductID =7 This is RelationID = 6

But further on the idea of ​​ProductID is never equal ToProductID CCTE.ProductID = j.ToProductID - Should not be executed, but everything works successfully. How INNER JOIN works in this case if there is no equality, or I don’t see it. Tell me how this design works.

1 answer 1

This is RelationID = 6

The first mistake. Not only 6 , but also 7 , 8 and 9 . Your recursion database consists of 4 lines.

But further on the idea ProductID is never equal ToProductID

The second mistake. You are apparently confused by the same column names, but here it is important to notice where the columns are:

 SELECT j.ProductID, j.ToProductID -- j! FROM CTE AS CCTE -- предыдущий шаг рекурсии INNER JOIN Relations AS j -- откуда выбираются записи ON CCTE.ProductID = j.ToProductID

The recursion step selects records from the entire Relations table . INNER JOIN selects those that are “needed” (in your objective sense) for the results of the previous recursion step in the CTE AS CCTE .

  • Selects the "next level" details from the baseline, with a ToProductID of 6 , 1 , 2 or 3 ( ProductID from the recursion base). Matches just one line, 5 5 [6] .
  • Selects the "next level" details from the previous step, with a ToProductID of 5 . Match RelationID 1 to 4 .