SQL subselect help needed... 1

[rojo]

I'm attempting to use the following query to return a sum of hours when a flag in the db is set to a certain code('P') or if its not set at all. The first subselect returns 3 records, the second only returns 1 record and the result is that all 3 records have the value from the second subselect in them. How can I change this so that the second subselect returns null for the 2 records that do not have a values.

thanks,
Rick

select a.TimeSheet_Employee_No Employee,
a.Non_Pulled_Hours Non_Pulled_Hours,
b.Pulled_Hours Pulled_Hours
from
(select TimeSheet_Employee_No, sum(TimeSheet_Hours) Non_Pulled_Hours
from TimeSheet, TimeSheet_Employees
where TimeSheet.TimeSheet_Employee_No = TimeSheet_Employees.Employee_Number
and TimeSheet_Seq_No <> 0
and (TimeSheet_Approved_SWT <> 'P' or TimeSheet_Approved_SWT is null)
and TimeSheet_Date Between To_Date('09/01/02', 'MM/DD/YY') and To_Date('09/28/02', 'MM/DD/YY')
Group by TimeSheet_Employee_No) a,
(select TimeSheet_Employee_No, sum(TimeSheet_Hours) Pulled_Hours
from TimeSheet, TimeSheet_Employees
where TimeSheet.TimeSheet_Employee_No = TimeSheet_Employees.Employee_Number
and TimeSheet_Approved_SWT = 'P'
and TimeSheet_Seq_No <> 0
and TimeSheet_Date Between To_Date('09/01/02', 'MM/DD/YY') and To_Date('09/28/02', 'MM/DD/YY')
Group by TimeSheet_Employee_No) b

 

[karluk]

I would try adding an outer join condition and match on TimeSheet_Employee_No.

select a.TimeSheet_Employee_No Employee,
a.Non_Pulled_Hours Non_Pulled_Hours,
b.Pulled_Hours Pulled_Hours
from
(select TimeSheet_Employee_No, sum(TimeSheet_Hours) Non_Pulled_Hours
from TimeSheet, TimeSheet_Employees
where TimeSheet.TimeSheet_Employee_No = TimeSheet_Employees.Employee_Number
and TimeSheet_Seq_No <> 0
and (TimeSheet_Approved_SWT <> 'P' or TimeSheet_Approved_SWT is null)
and TimeSheet_Date Between To_Date('09/01/02', 'MM/DD/YY') and To_Date('09/28/02', 'MM/DD/YY')
Group by TimeSheet_Employee_No) a,
(select TimeSheet_Employee_No, sum(TimeSheet_Hours) Pulled_Hours
from TimeSheet, TimeSheet_Employees
where TimeSheet.TimeSheet_Employee_No = TimeSheet_Employees.Employee_Number
and TimeSheet_Approved_SWT = 'P'
and TimeSheet_Seq_No <> 0
and TimeSheet_Date Between To_Date('09/01/02', 'MM/DD/YY') and To_Date('09/28/02', 'MM/DD/YY')
Group by TimeSheet_Employee_No) b
where a.TimeSheet_Employee_No = b.TimeSheet_Employee_No (+);

 

[rojo]

Thanks karluk, that did it. I had tried the outer join but it just wasn't in the right place.

thanks again, you've been very helpful.