sql server - (T-SQL): How do I use result set A to calculate result set B? -

i have 2 queries, , b, use independently each other. both return employee id , metric values. want use employee ids returned result set in query b.

a's query structured this:

select employee_id employee employee_team = 1 , employee_role = 1 

b's query structured this:

declare @tester int = 123450 --plug in employee id select employee_id ,employee_name ,sum(case    when notes.note_author!=employee_id , logs.log_date<@today    1 else 0 end) metric notes inner join note_to_log_bridge br on notes.note_id=br.note_id inner join logs on br.log_id=logs.log_id inner join employee on employee_id=@tester 

if want b's metrics 5 employees, have run query b 5 times, changing @tester variable each time. i'd instead find way of automating that, metrics query b every employee_id in result set a.

i tried stored result set cte , using while loop run through query b:

declare @line=1 cte (employee_id) <query_a> while (@line<=count(cte.employee_id)) begin <query b>... 

i never finished query because discovered while cannot follow creation of cte.

i tried using table variable:

declare @set_a (employee_id int) insert @set_a <query a> 

but when try use @set_a in query b, message saying need declare scalar variable @set_a.

i tried using temp table , got "could not bound" error.

i out of ideas. approaching problem in resembling right direction? possible?

thank you!

use cursor ?

if want b's metrics 5 employees, have run query b 5 times, changing @tester variable each time.

declare @empid int; declare vend_cursor cursor     select employee_id employee employee_team = 1 , employee_role = 1 open vend_cursor fetch next vend_cursor @empid; while @@fetch_status = 0     begin   // query @mpid  fetch next db_cursor @name    end    close vend_cursor deallocate vend_cursor