I have a table with the following schema
(Id(int PK),EmployeeId(int),DepartmentId(int),IsSelfAccessToDepartment(bit))
this table can contain the following entries
(1,101,21,1)
(2,101,22,0)
(3,102,21,1)
(4,103,21,1)
(5,103,22,0)
I want to retrieve only those employee Ids where the IsSelfAccessToDepartment=1 i.e. if the same employee Id has another entry where IsSelfAccessToDepartment=0 in that case this row should not be retrieved.
The problem is I want to retrieve this information without using any sub queries and joins cause it will create performance issues as this table will be having millions of entries.