Why does RequestStatus have a NULL value ONLY when an employee logs into system?

Transact-SQL
1

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

2

Since you are using an OUTER JOIN to get the Request data, if there is no request, those data items will be returned as NULL.

3

Hi Stephen,

Thank you for your time to respond.

I am aware of that but as stated in my original question, there is not a single record with a null value for RequestStatus.

Recall that I stated that there are three possible values, Pending, Completed and Cancelled.

As soon as a request is submitted, the default value is Pending.

The issue is that if manager or director or personnel logs in with their accounts, the get correct value of either pending, completed or cancelled.

If an employee who is not manager or director or personnel logs in with his/her own account, the value is always NA, meaning that it is coming out as NULL and this is not correct.

Something is wrong with that query but unfortunately, I am having issues figuring out what the problem is.

4

You are missing Stephen's point. The NULL doesn't exist in your table. It exists because of the OUTER JOIN since there wasn't a matching row in the RIGHT table for the LEFT table. Switch it to an INNER JOIN and you'll see that the row from the LEFT table doesn't exist at all. It shows up in the OUTER JOIN query with the data from the LEFT table and NULLs for the RIGHT table.

5

Ahh got it!

So, how do I resolve this then?

Is it by another another OR predicate like instead of this:

LEFT JOIN Request r ON r.managerID = e.EmpID OR r.DirectorID = e.EmpID

Then this:

INNER JOIN Request r ON r.managerID = e.EmpID OR r.DirectorID = e.EmpID OR a.empnum=e.empnum ??

Thanks for your help Tara

6

We would need to see sample data to be able to say how to resolve it. Sample data that illustrates the issue.