I don't seem to know what I'm doing. I was hoping the following would return all records and sums but it doesn't seem to do a full outer join on the columns identified. I'm joining the same table to itself.
I know that there are records for both stat_type = 192 and stat_type = 191 but that they do not have common keys. how do I fix this?
select sum(a.stat_value),sum(a.stat_overtime),sum(c.stat_value),sum(c.stat_overtime),a.home,trunc(a.entry_date),b.user_name,decode(a.SUPERVISION,0,'ITSTNS','ITSTS') from tbl_datax a, tbl_users b, tbl_datax c
where b.user_id = a.entry_posted_by
and a.stat_type = 192
and c.home = a.home
and trunc(c.entry_date) = trunc(a.entry_date)
and c.entry_posted_by = a.entry_posted_by
and c.stat_type = 191
and decode(c.SUPERVISION,0,'ITSTNS','ITSTS') = decode(a.SUPERVISION,0,'ITSTNS','ITSTS')
group by a.home,trunc(a.entry_date),b.user_name,decode(a.SUPERVISION,0,'ITSTNS','ITSTS');
appreciate any assistance!