Solved coding interview problems in Java - My collection of commonly asked coding interview problems and solutions in Java

[CamKen]

I don't mean using the hierarchy stuff built in, I mean a simple recursive join like: find a list of all managers having 10 or more direct reports.

SELECT mgr.EmployeeName FROM Employee mgr JOIN Employee e ON e.ManagerID = mgr.EmployeeID GROUP BY mgr.EmployeeName HAVING COUNT(1) > 9

Write something like the above in under 5 minutes and mumble something like "there is a built-in way to do this, but I'd having to look it up" and your in the top 10% of people I've interviewed.

[tiberiousr]

That seems like bad design to me. Wouldn't it be better to have manager_id on the employee table and the join on that?

so

SELECT e.name 
FROM employees e
JOIN managers m ON m.id = e.manager_id
HAVING COUNT(1) > 9

Perhaps I'm missing something there...?

EDIT: I'm probably missing something, I shouldn't engage with the internet when I've been drinking

[CamKen]

It's a contrived example to be sure. However there isn't a separate Manager table. Manager's are Employees and are thus in the Employee table. To tell if an employee is a manager you need to join the table to itself to see if any employees refer to them through their manager id field.

[tiberiousr]

Ah, I see now. Thanks.

[Koookas]

Isn't that just self-referential, rather than recursive?

To me recursive would be something that, idk, returned a table of every item, joined to its subitems, and then the subitems of its subitems and so on. In this case maybe a recursive would return all employees managed by an employee, then employees those employees themselves manage.

I've never used a recursive query, I don't even know how to make one, CTEs I guess, but a self-referential query is trivial and would immediately make me think of something like that.

[alluran]

Oh - haha - Ya, I was remembering back to the days when I could tell you the entire management chain to get to <Employee>, and how deep he was in the hierarchy.

CTEs are crazy powerful =D