Hello, I encountered such a problem: I have several employees, one of whom works in the Main Branch, and another employee works in the Main Division subordinate. I try to write a request that would output all employees who work in the Main Division and subordinate offices. However, as a result, I only get an employee of the Main Office. Here is an example of the request: http://sqlfiddle.com/#!17/5f7eb/2

enter image description here How can I fix it?

  • And what can be the level of nesting units? judging by the scheme, a recursive query is needed here, because the level of nesting is not limited. And you did not say in what form to withdraw - Mike
  • The level of nesting units is as follows: there may be many Main units which may have many subordinates. Moreover, the subordinate units are unique. The following ID of the employee, the full name, the main subdivision, and if the employee works in the subordinate, the name of this department should be output. - Sergey Zavada
  • You did not answer the question, what is the level of nesting, i.e. whether a subordinate unit can have a subordinate, and that one has another subordinate. And what then to deduce in the name of divisions, a way? ("Main / Main Subordinate / Subordinate Subordinate") - Mike
  • It can be the only way (Main / Subordinate of the main) (Main 2 / Subordinate of the main 2) - Sergey Zavada

1 answer 1

If you correctly understood the question and the nesting level is one (which follows from the fact that you are trying to use a regular join, and not a recursive query), then this is how:

SELECT e.ID, s.DeptName, ss.DeptName, e.FullName FROM StructuralSubdivision AS s JOIN StructuralSubdivision AS ss ON s.DeptKod IN (ss.PKod, ss.DeptKod) JOIN ListPositionSubdivision AS ls ON ls.NameSubdivision=ss.DeptKod JOIN Employees AS e ON e.ID = ls.IDEmployees JOIN Position AS p ON ls.NamePosition = p.ID WHERE s.DeptName = 'Главное подразделение' AND p.Name = 'Доцент'

And if not, then describe the normal structure, with such field names it is impossible to understand anything at all, why, for example, fields that clearly contain IDs are called Name?

  • Yes, everything works correctly only as you can do so if SubDept = MainDept then SubDept = NULL sqlfiddle.com/#!17/5f7eb/26 - Sergey Zavada
  • @SergeyZavada sqlfiddle.com/#!17/5f7eb/31 - Mike 10:20 pm
  • And now the question is different. If AND s.DeptName = 'Main unit' is removed from the condition, then the query displays the following sqlfiddle.com/#!17/5f7eb/39 and GROUP BY does not help, how can this be fixed? - Sergey Zavada
  • @SergeyZavada s.PKod is null - Mike
  • Thank you very much) - Sergey Zavada February