It is necessary in this query to attach goods, but so that they are joining the last level of the hierarchy.

SELECT k.Kls_unicode ,k.Kls_Name AS '1' ,ka2.Kls_Name AS '2' ,ka3.Kls_Name AS '3' ,ka4.Kls_Name AS '4' ,ka5.kls_name AS '5' ,ka6.Kls_Name AS '6' ,CASE WHEN k.Kls_Level = 0 AND k.Kls_Parent = 0 THEN k.kls_name WHEN k.Kls_UniCode = ka2.Kls_Parent THEN ka2.Kls_Name WHEN ka2.Kls_Parent = ka3.Kls_UniCode THEN ka3.Kls_Name WHEN ka3.Kls_Parent = ka4.Kls_UniCode THEN ka4.Kls_Name WHEN ka4.Kls_Parent = ka5.Kls_UniCode THEN ka5.Kls_Name WHEN ka5.Kls_Parent = ka6.Kls_UniCode THEN ka6.Kls_Name END FROM Kls_All AS k LEFT JOIN Kls_All AS ka2 ON ka2.kls_parent = k.Kls_UniCode LEFT JOIN Kls_All AS ka3 ON ka3.kls_parent = ka2.Kls_UniCode LEFT JOIN Kls_All AS ka4 ON ka4.kls_parent = ka3.Kls_UniCode LEFT JOIN Kls_All AS ka5 ON ka5.kls_parent = ka4 .Kls_UniCode LEFT JOIN Kls_All AS ka6 ON ka6.kls_parent = ka5.Kls_UniCode LEFT JOIN KlsCmpLnk AS kcl ON kcl.Kls_UniCode = k.Kls_UniCode WHERE k.Kls_Level = 0 AND k.Kls_Parent = 0 AND k.Kls_Name LIKE '%область применения%' ORDER BY k.Kls_Name

enter image description here

  • Well, join the products to the last table in your left join. As you can’t say more, you didn’t even explain what "goods" are to be attached. In general, the query should be rewritten using recursive CTE so as not to produce a bunch of left join. - Mike
  • It is necessary to bind the goods in groups, for example, on the first line: Means to reduce the weight and to it all the goods, and in the second line it is necessary to bind to group 3, etc. - Digi
  • And how are you going to show "all the goods" in one line? The number of rows will of course multiply when linked ... And yes, if you rewrite the request for a recursive CTE, then any bindings will go to the current nesting level, i.e. you get exactly what you want - Mike
  • In my opinion, a long CASE easily replaced by COALESCE(ka6.Kls_Name, ka5.Kls_Name, ka4.Kls_Name, ka3.Kls_Name, ka2.Kls_Name, ka.Kls_Name,) , not? All the more so because "according to the last" ... - Akina
  • although maybe I misunderstood you and you just don’t need the current level ... in any case, show the structure of the tables and how they should be glued (by what field) as it turns out that you have to take care that the goods are attached to the desired level of hierarchy, After all, the idea is that the goods should have an id of a specific branch, and it is to them that they will become attached ... - Mike

0