create procedure dbo.EmployeesForTask
   @task_id int,
   @dept_id int
as
   select
      e.Name,
      case 
         when est.employee_id is null then 'FALSE'
         else 'TRUE' 
      end as SelectedForTask
   from
      Employee e
      left join EmployeeSelectedForTask est
         on e.employee_id = est.employee_id
   where
      e.dept_id = @dept_id
      and est.task_id = @task_id

quote:

---

Originally posted by dkekesi

OK, I thought I had it going but not. If I select employee1 at task1 and select employee2 at task2 and execute the query, I'll get both employees selected for both tasks. I've been thinking about this for a while now but I simply cannot wrap my head around the problem.
Please help as I am going nuts.

Best Regards,
Daniel

---

--- CREATE TABLES
create table  dbo.Department 
(
   DepartmentId int not null primary key clustered, 
   DepartmentName varchar(255) not null
);

create table  dbo.Employee 
(
   EmployeeId int not null primary key clustered, 
   DepartmentId int not null references Department(DepartmentId), 
   EmployeeName varchar(255) not null
);

create table dbo.Task 
(
   TaskId int not null primary key clustered, 
   DepartmentId int not null references Department(DepartmentId), 
   TaskName varchar(255) not null
);

create table dbo.EmployeeSelectedForTask
(
   TaskId int not null references Task(TaskId), 
   EmployeeId int not null references Employee(EmployeeId),
   primary key clustered(TaskId, EmployeeId)
);

-- INSERT TEST DATA
insert into Department values (1, 'Development');
insert into Department values (2, 'QA');
insert into Department values (3, 'Configuration');

insert into Employee values (1,1,'Sunita Beck');
insert into Employee values (2,1, 'D. Kekesi');
insert into Employee values (3,1, 'William Gates');

insert into Task values (1, 1, 'Create Windows 8');
insert into Task values (2, 1, 'Create SQL Server 2014');
insert into Task values (3, 1, 'Create IE 11');

insert into EmployeeSelectedForTask values(1,1);
insert into EmployeeSelectedForTask values(2,2);

GO

-- CREATE STORED PROC (I CHANGED THE JOIN CONDITIN ON THE STORED PROC FROM THE ORIGINAL)

create procedure dbo.EmployeesForTask
   @task_id int,
   @dept_id int
as
   select
      e.EmployeeName,
      case 
         when est.EmployeeId is null then 'FALSE'
         else 'TRUE' 
      end as SelectedForTask
   from
      Employee e
      left join EmployeeSelectedForTask est
         on e.EmployeeId = est.EmployeeId and est.TaskId = @task_id
   where
      e.DepartmentId = @dept_id
GO

-- TEST THE STORED PROC
exec dbo.EmployeesForTask 1, 1
exec dbo.EmployeesForTask 2, 1

GO


-- CLEANUP
drop procedure dbo.EmployeesForTask;
drop table dbo.EmployeeSelectedForTask;
drop table dbo.Task ;
drop table dbo.Employee;
drop table dbo.Department;