Hello experts,
I am stumped by this stupid code.
We have several fieldnames but two significant ones are ManagerID and SupervisorID
Supervisor is over Managers and Managers are over employees.
Manager managers one group and Supervisor manages all the groups called dept.
We would like display records belonging to a supervisor or his/her department.
The following code keeps displaying all employees under a manager.
Not sure why.
select distinct d.SupervisorName,a.filenameName,a.filePath,
e.empID,e.employeeName,e.department,e.unitName from Angulers as a
inner join Employee as e on e.empnum= a.empnum
left join departmentheads d on e.deptnum =d.deptnum
where d.supervisorID = @supervisor
Without some sample data and expected outputs it is difficult to know what you are aiming at or why you are not getting it. I will note that even though you have "left join"ed the DepartmentHeads table you have, in effect, an inner join since you are requiring that table to have data due to the logic in the WHERE clause.
I have to agree with Stephen above. It's nearly impossible to do anything for you except to take a shot in the dark.
Remembering that this is just a shot in the dark, the code looks like it's currently written to return everyone for all departments for a given supervisor, thus meeting your requirements EXCEPT if employees have no entries in the Angulers table (whatever that may be). If only one manager's employees have entries in the Angulers table, then the inner join will prevent the return of other employees. Perhaps an additional outer join is necessary.
As a bit of a sidebar, if you need to use DISTINCT on something like this, then there's usually something wrong with the data, the table design, or both. To be honest, I'd maintain all levels in a Positionnal Adjacency List and use Nested Sets to query the data very efficiently.