0

Hello and thank you for any help you can provide.

I am running on a shared-server platform so I am restricted to using mySQL version 5.6.46 which means I do not have access to "recursive CTE" and "WITH" commands . After many hours of looking for a solution for how I can perform a recursive-type operation - and after trying many approaches that did not end up working - I found this Stack Overflow reference to an approach that seems like a reasonable method for what I am trying to accomplish (and simple enough for someone at my level of understanding). The method described can be found about 1/4 of the way down the page under the title of "Alternative 3: Repeated Self-joins."

In essence, I have copied the entire recommended mySQL query and modified it to my table structure BUT it triggers an error and does not run.

The error is "#1054 - Unknown column 'p2.id' in 'on clause'" which - according to my internet searches - is a common error with many references BUT each refers refers to a two-table structure with a 'comma' that - based on a SQL update years ago now requires parentheses to make it work. I am puzzled with this error on my end because I DO NOT have a two-table structure. I can find no other reason for this error. In fact, on the https://www.piliapp.com/ syntax checker it shows the syntax is fine. I would appreciate your eyes on this query to see what I'm missing. Here is my code:

select      p4.ManagerID as parent4_id,
            p3.ManagerID as parent3_id,
            p2.ManagerID as parent2_id,
            p1.ManagerID as parent_id,
            p1.EmployeeID as employee_id,
            p1.LastName
from        cps_db_sm7.dEmployees p1
left join   cps_db_sm7.dEmployees p2 on p2.id = p1.ManagerID
left join   cps_db_sm7.dEmployees p3 on p3.id = p2.ManagerID
left join   cps_db_sm7.dEmployees p4 on p4.id = p3.ManagerID 
where       23578 in (p1.ManagerID, 
                   p2.ManagerID, 
                   p3.ManagerID, 
                   p4.ManagerID)
ORDER BY 1,2,3,4,5;

The error is triggered by the first LEFT JOIN and is in reference to the ON p2.id. Could someone please look at this and suggest a solution? Thank you!

Neil_Tinkerer
  • 119
  • 8
  • 2
    Do you have a column called `id` in table `cps_db_sm7.dEmployees`? – GMB Feb 18 '20 at 09:08
  • No. The table structure has EmployeeID as the primary key and ManagerID but no "id" on its own. – Neil_Tinkerer Feb 18 '20 at 11:02
  • OK so you just want to replace `id` with `EmployeeID` in your query, and that should be fine. Voting to close this as a typo. – GMB Feb 18 '20 at 12:22
  • 1
    I feel foolish ... I spent too much time starring at this looking for another problem when it was something very basic. Thank you for your help! Very much appreciated. – Neil_Tinkerer Feb 18 '20 at 12:44

0 Answers0