All geek questions in one place

How can I make this relationship between parents and children work?

Using MySQL, how can I make this hierarchy work?

  • Parent ID is 100. This parent has ParentID 0.
  • The child has the identifier 101. ParentID is 100.
  • SubEntity has an identifier of 105. ParentID is 100.
  • The child of the subobject has an identifier of 106. Their ParentID is 105.

This request will be connected to iReport. Currently, Subentity and its children do not collapse into Parent.

Here is what I ended up with:

`Select case when FC.ParentType = 'PARENT' then FC.FundCode when FB.ParentType = 'PARENT' then FB.FundCode when F.ParentType = 'PARENT' then F.FundCode else 0 end as `ParentID`, case when FB.ParentType = 'SUBFUND' then FB.FundCode when F.ParentType = 'SUBFUND' then F.FundCode else 0 end as `SubfundID`, case when FB.ParentType = 'CHILD' then FB.FundCode when F.ParentType = 'CHILD' then F.FundCode else 0 end as `Children`, F.FundName From Fund F join Fund FB on F.ParentId = FB.FundCode join Fund FC on FB.ParentID = FC.FundCode`
+4
source share
2 answers

Is there a static number that determines how many levels this parent-child relationship has?

Yes: Use the recursive LEFT JOIN X times.

 SELECT * FROM table t1 LEFT JOIN table t2 ON t1.id = t2.parent_id LEFT JOIN table t3 ON t2.id = t3.parent_id ...

No: Do this with separate queries until you select the parent / child objects as you like. Make sure you have checks to avoid cycles, i.e. the child is the parent of the parent.

0
source

For this scenario, you can use Recursive CTE . Take a look at this link, this gives a good example.

http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/

0
source

Source: https://habr.com/ru/post/1442102/

More articles:


All Articles