Sql questions about self join

I have question regarding SQL join for MySQL.

Let suppose I have following table A and parent-child relation ship. How can I get all children (including sub children of children till leaf node) given parent id ?

Table A
child id     parent id 
1            1
2            1
3            1
4            2
5            3
8            9

From the above table, if we given the parent (1) I have to query such that given the parent id(1) it gives all hierarchy example:

child id     parent id 
1            1
2         开发者_开发技巧   1
3            1
4            2
5            3

One of the restriction is I cannot change the data-model because the data model is JBPM databse tables (open source) I am adding some functionality for ProcessInstancelog table in JBPM graph for sub-process (for each fan-out process).

---

It appear that you want to be able to select a parent, and then get all of its children, regardless of where its at in the hierarchy.

The most efficient way is to use a reverse association table. In which each child would be associated with its parent, grant parent, and so on until you reach the root of the tree.

This obviously means you have to do more work on write to maintain the table, but it will save you from complexity on querying the table.

Example

id       parent
1        0
2        1
3        1
4        2

Reverse table

child     parent    level
2         1         1
3         1         1
4         2         1
4         1         2

With that you could do a subselect in your query with (select child where parent = 1)

---

You can do this in sql like this.

CREATE TABLE [dbo].[Employee](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[managerId] [int] NULL,

)  

  WITH UserCTE AS (
    SELECT id, name, managerId,0 AS steps
     FROM dbo.[Employee]
     WHERE id = 1 

 UNION ALL

  SELECT mgr.id, mgr.name, mgr.managerId, usr.steps +1 AS steps
  FROM UserCTE AS usr
   INNER JOIN dbo.[Employee] AS mgr
     ON usr.managerId = mgr.id
)
SELECT * FROM UserCTE  order by id;