Greetings SQL gurus.
select
IsNull(r.mgrid,'NA') mgrid,
IsNULL(r.RequestStatus,'NA') AS RequestStatus,
h.deptname,
isNull(p.DirectorName, 'N') DirectorName,
IsNull(r.DirectorID,'NA') DirectorID,
h.DeptNum deptNumber,
E.DeptNum,
E.Department,
E.EmpName,
E.EmpID,
E.Password,
h.empname as director
from Employee e
LEFT OUTER JOIN DeptHeads h on e.EmpID = h.directorId
LEFT OUTER JOIN Personnel p on e.EmpID = p.EmpID
LEFT JOIN Request r ON r.managerID = e.EmpID OR r.DirectorID = e.EmpID
where e.EmpID = 'myUsername' and e.SSN = 'mypassword' order by e.department asc
Per code above, Upon submitting a request, employee's ID as well as his/her manager's ID, Director's ID and PersonnelManagerID are saved to the database.
There is a field called RequestStatus with three options, Pending, Complete or Cancelled.
By default, RequestStatus is pending.
Once RequestStatus changes to Complete, this request is routed to the manager.
Similarly, once manager's decision is in Complete Status, the request is routed to the Director who does his/her part and routs to Personnel Director for final action.
There are four tables involved here.
Employees table with employeeID, employeeName, EmployeeUsername and employeePassword, among other field names. These are the relevant ones.
There is the DeptHead table.
This table contains DirectorID, DirectorName, among other fields. These are the relevant fields.
Personnel with PersonnelManagerID
Then there is the Request table.
This is the table where requests are submitted and it includes employeeID, managerId, DirectorID and requestStatus.
All Employees including Manager, Director and Personnel Manager are all in Employee's table as empname and Empnum.
Employee ID is saved as empID in Request table Manager's employee ID is saved as ManagerID in Request table Directors Employee Id is saved as DirectorId in Request table
Personnel Managers employeeID is saved as PersonneID in Request table
If an employee logs in, his/her ReqeustStatus should either be Pending, Complete or Cancelled.
Right now, when I log in as an employee, RequestStatus is null even though there is no null value on the database. When I log in as a manager or director or personnel, I get correct value for RequestStatus.
Any ideas what I need to change on the query below.
Thanks very much in advance