mysql 查询递归自身,带有递归自联接的MySQL查询

I am trying to write a query with a recursive self join. My tables are something as below:

Table1

EMP_ID Name

1 Manger1

2 Manger2

3 Manger3

4 Employ1

5 Employ2

Table2

Par_EMP_ID EMP_ID

1 2

2 3

3 4

3 5

Here in above tables the Manager1, Manager2, ..., Employe2 are employees in a company where Manager1 is Head of Dept. Manager2 is reporting to Manager1, Manager3 is reporting to Manager2, this relation ship is maintained in table2 where Par_EMP_ID is EMP_ID of reportii and column EMP_ID is id of reporter. Now I want result as below:

Manager1 Manager2 Manager3 Employe1

Manager1 Manager2 Manager3 Employe2

解决方案

UPDATE:

According to your specs, here's the solution:

SELECT e.names member, d.name child

FROM MEMBERS d INNER JOIN

(SELECT a.id, GROUP_CONCAT(c.name) NAMES

FROM MEMBERS a

INNER JOIN RELATIONSHIP b ON a.id = b.MEM_ID

INNER JOIN MEMBERS c ON c.id = b.PAR_MEM_ID

GROUP BY a.id) e ON e.id = d.id

Result:

| MEMBER | CHILD |

|--------------------|--------------|

| Great Grand Father | Grand Father |

| Grand Father | Father |

| Father | Child1 |

| Father | Child2 |

Note:the results may depend on your sample data. And I updated also the SQLFiddle below so you might want to check that out.

Here's the SQLFiddle.