Mysql query with recursive self JOIN

Question

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

Read mysql how to find the total number of child rows with respect to a parent — Grijesh Chauhan
– Grijesh Chauhan, Commented Oct 8, 2013 at 9:22

Mark · Accepted Answer · 2013-10-11 09:57:55Z

1

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.

edited Oct 11, 2013 at 9:57

answered Oct 8, 2013 at 9:47

Mark

8,47115 gold badges52 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Xeoncross Over a year ago

Nice answer. What if I only wanted to see if there was a relationship chain between Child2 and Great Grand Father? (Rather than every relationship?)

Amit Kumar Pawar Over a year ago

@Xeoncross got any answer

Collectives™ on Stack Overflow

Mysql query with recursive self JOIN

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related