I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information:
SELECT (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!
TIA
BUMP... Somebody...|||Okay, I tried adding the ISNULL to the statement, but I think the problem is because until a request has been put in there is nothing linking the employee table for the JOIN on the request table. When they put in a request it adds an entry to the request table for them. Up till that point, there will be nothing matching the two tables.
Here is my statement as it stands now. Is there anyway to get the results to show if the INNERJOIN isn't finding any results in the request table?
SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname
I know it seems I am just talking to myself at this point, but I would LOVE for someone to join my conversation. Thanks in advance for any help!!!
Okay, I figured it out. Had to switch my query to a LEFT OUTER JOIN.
SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeLEFTOUTER JOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname
oapException