SQLTeam.com | Weblogs | Forums

Can I integrate the second query with the first?

I have code that gets me the last time a staff logged in to the system.

First Query:
select max(a.session_start) as session_start, max(a.session_end) as session_end, b.last_name, b.first_name from use_log_with_transactions_view a
inner join staff_view b
on a.staff_id = b.staff_id
group by b.last_name, b.first_name
order by b.last_name, b.first_name

But i need to get the worker_role the staff has. Can I integrate the below query with the one above? I need the worker_role bec I need to filter the report.
To do that I would use this query:
Second query:
select a.last_name, a.first_name, c.description from staff_view a
left join staff_worker_role_link_view b
on a.staff_id = b.staff_id
inner join worker_role c
on c.worker_role_id = b.worker_role_id

Yes, you can include it in the first query by getting max(description). Sorry, I just can't do the generic "a", "b", "c", stuff, so I adjusted the query accordingly. Makes it MUCH MUCH easier to combine queries later.

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 use_log_with_transactions_view ul
inner join staff_view sv
on ul.staff_id = sv.staff_id
left outer join staff_worker_role_link_view sw
on a.staff_id = sw.staff_id
inner join worker_role wr
on wr.worker_role_id = sw.worker_role_id
group by sv.last_name, sv.first_name
order by sv.last_name, sv.first_name
1 Like

thank you Scott, that is a thing of beauty.