2

I am required to rewrite a query in MySQL which is originally written in MS SQL Server using the WITH clause. This is basically to fetch all levels of parent records for child records. Here, I am using the classic EMPLOYEES table of the HR schema in Oracle database as an example.

Originally data in EMPLOYEES table is in this format.

select employee_id, manager_id
from employees
order by 1,2;

---------------------------------------------
EMPLOYEE_ID            MANAGER_ID             
---------------------- ---------------------- 
100                                           
101                    100                    
102                    100                    
103                    102                    
104                    103                    
107                    103                    
124                    100                    
141                    124                    
142                    124                    
143                    124                    
144                    124                    
149                    100                    
174                    149                    
176                    149                    
178                    149                    
200                    101                    
201                    100                    
202                    201                    
205                    101                    
206                    205

My requirement is to view all level of parent records for child records. I am able to achieve this using the following query in Oracle and MS SQL Server.

WITH Asd(Child,
     Parent
    )
AS (SELECT Employee_Id,
           Manager_Id
      FROM Employees
    UNION ALL
    SELECT E.Employee_Id,
           A.Parent
      FROM Employees E, Asd A
      WHERE E.Manager_Id = A.Child
   )
SELECT Child,
       Parent
  FROM Asd
  WHERE Parent IS NOT NULL
  ORDER BY Child, Parent;

----------------------------------------------------------
CHILD                  PARENT                 
---------------------- -----------------------------------
101                    100                    
102                    100                    
103                    100                    
103                    102                    
104                    100                    
104                    102                    
104                    103                    
107                    100                    
107                    102                    
107                    103                    
124                    100                    
141                    100                    
141                    124                    
142                    100                    
142                    124                    
143                    100                    
143                    124                    
144                    100                    
144                    124                    
149                    100                    
174                    100                    
174                    149                    
176                    100                    
176                    149                    
178                    100                    
178                    149                    
200                    100                    
200                    101                    
201                    100                    
202                    100                    
202                    201                    
205                    100                    
205                    101                    
206                    100                    
206                    101                    
206                    205                    

36 rows selected

As you can see, I am bringing all the parents as well as grand parents under PARENT column in the query.

However, this approach does not work in MySQL as WITH clause is not supported. Could anyone please help me on how to rewrite this query in MySQl?

Yaroslav
  • 6,476
  • 10
  • 48
  • 89
Sanjeev Behera
  • 21
  • 1
  • 3
  • Can you sepcify what exactly your query is supposed to do? Do you need all levels of transivity or just a certain amount of steps in the hierachy? – Manuel Schweigert Sep 25 '12 at 11:42
  • The result (all parent records for child) that I am getting using the second query (using WITH clause and recursive query), I want exact same result in MySQL without using WITH clause. My query and result set above are clear enough, I believe. – Sanjeev Behera Sep 25 '12 at 13:52
  • Do you have a maximum possible depth of the tree? If so, the only thing I can think of is multiple outer joins on the table up to the possible maximum. –  Sep 25 '12 at 15:08
  • There is no possible maximum depth here. In fact, in production env, user can create any depth. So the WITH clause was able to handle it without problem, working recursively through it. I also thought of multiple outer joins, but that would be hard coding. Thank you. – Sanjeev Behera Sep 26 '12 at 05:17

1 Answers1

0

As for sept/2012, there is not direct substitute on MySQL for the Common Table Expressions (CTE) of MS SQL Server. You should rely on stored procedures.

Check this answer to another identical question here in SO. It should answer all your doubts. I'm not copying any code as on the link everything is well detailed and explained.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Yaroslav
  • 6,476
  • 10
  • 48
  • 89