I want to capture all staff even without a login log record-CodePudding

IN this SQL we are getting all staff who have a login history from use_log_with_transactions_view). But I need to get all staff even if they have no login history. Login history is in use_log_with_transactions_view. I've tried outer, full, right joins but they don't connect on the = part ( on ul.staff_id = sv.staff_id). Is there any way i can get a staff who never logged in to the system?

select max(ul.session_start) as session_start, max(ul.session_end) as session_end, sv.last_name, 
sv.first_name,
    max(wr.description) as description
from staff_view sv
full join  use_log_with_transactions_view ul
on ul.staff_id = sv.staff_id
inner join staff_worker_role_link_view sw
on ul.staff_id = sw.staff_id
inner join worker_role wr
on wr.worker_role_id = sw.worker_role_id
where wr.description in ('Program Leader/Supervisor', 
'Program Administration', 'Licensed Clinician', 'Unlicensed Clinician','Service Provider')
and sv.first_name <> 'NTST' and sv.is_administrator = '0' and sv.end_date is null
group by sv.last_name, sv.first_name
order by sv.last_name, sv.first_name

CodePudding user response：

When you want SQL to look for what's not there?
Then one way is to match all that should exist, against what exists.

Sometimes that involves a cross join.

But here you can just left join the staffs in the logs to the staff.
Those missing in the logs won't match on staff_id.

select staff.last_name, staff.first_name
, max(uselog.session_start) as session_start
, max(uselog.session_end) as session_end
, max(wrole.description) as description
from staff_view as staff
join staff_worker_role_link_view as staffrole
  on staffrole.staff_id = staff.staff_id
join worker_role as wrole
  on wrole.worker_role_id = staffrole.worker_role_id
left join (
  select staff_id
  , max(session_start) as session_start
  , max(session_end) as session_end
  from use_log_with_transactions_view
  group by staff_id
) uselog
  on uselog.staff_id = staff.staff_id
where wrole.description in ('Program Leader/Supervisor', 'Program Administration', 'Licensed Clinician', 'Unlicensed Clinician', 'Service Provider')
  and staff.first_name <> 'NTST' 
  and staff.is_administrator = '0' 
  and staff.end_date is null
-- AND uselog.staff_id IS NULL -- uncomment for only those without logs
group by staff.last_name, staff.first_name
order by staff.last_name, staff.first_name

CodePudding user response：

If you want ALL Staff regardless of a login, you only need to do a LEFT JOIN, meaning, I want all records from the LEFT table (listed first), regardless of a record found in the right table (listed second.

Cleaned-up readability, but now you can see visually the hierarchy of how SV gets to UL, notice that is left-join. So you always get the SV record, but only those based on the WHERE condition associated with the SV.

Notice I moved the WR.Description to that respective JOIN portion. This way, it does not bind the left and right as an INNER join because it is not part of the overall WHERE to the SV. It is only applicable to the join component from the SW before it.

All that said, for those records that come through and do NOT have a UL (login), they will have NULL values for the session start/end and worker description. You can apply a coalesce() to those as you seed fit.

select 
        sv.last_name, 
        sv.first_name,
        max(ul.session_start) as session_start, 
        max(ul.session_end) as session_end, 
        max(wr.description) as description
    from 
        staff_view sv
            LEFT join use_log_with_transactions_view ul
                on sv.staff_id = ul.staff_id 
                inner join staff_worker_role_link_view sw
                    on ul.staff_id = sw.staff_id
                    inner join worker_role wr
                        on sw.worker_role_id = wr.worker_role_id
                        AND wr.description in ( 'Program Leader/Supervisor', 
                                                'Program Administration', 
                                                'Licensed Clinician', 
                                                'Unlicensed Clinician',
                                                'Service Provider')
    where
            sv.first_name <> 'NTST' 
        and sv.is_administrator = '0' 
        and sv.end_date is null
    group by 
        sv.last_name, 
        sv.first_name
    order by 
        sv.last_name, 
        sv.first_name