There is no recursion in my CTE SQL request

• Alex1337

  SSC Enthusiast

  Points: 188

  More actions

  September 1, 2022 at 8:26 pm

  Go to Answer

  #4083028

  I have very simple table

  

  I want to receive table with i,id and text column like 67->304->303->302->301->300->299

  I don't understand What wrong in my CTE?

  WITH RECURSIVE

  CTE (CTE_i, CTE_Id, CTE_ParentID, CTE_ParentI, Arrow) AS

  (

  Select i, Id, NULL, NULL,'.' FROM DockerImage where ParentId =''

  UNION ALL

  Select DockerImage.I, DockerImage.Id, DockerImage.ParentId, CTE_i , CONCAT (CTE.Arrow, '->', DockerImage.I) FROM CTE

  join DockerImage on CTE_ParentId=DockerImage.id

  )

  SELECT * FROM CTE;

  Result of this SQL is wrong and I have no recursion.

  

   

   

  • This topic was modified 2 years, 2 months ago by  Alex1337.

• sandwich

  Grasshopper

  Points: 15

  More actions

  September 1, 2022 at 9:56 pm

  Answer

  #4083058

  I think your join is backwards. The initial select has blank ParentID, so the join needs to be from cte.id to DockerImage.ParentID.

  JOIN    DockerImage  ON CTE.CTE_id = DockerImage.Parentid

• Alex1337

  SSC Enthusiast

  Points: 188

  More actions

  September 2, 2022 at 7:47 am

  #4083156

  Thank you, its working !!!

  

   

  • This reply was modified 2 years, 2 months ago by  Alex1337.
  • This reply was modified 2 years, 2 months ago by  Alex1337.