How to select individual records where conditions without using subqueries

Question

I have a table with the following diagram

(Id(int PK),EmployeeId(int),DepartmentId(int),IsSelfAccessToDepartment(bit))

this table may 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 get only those employee IDs where IsSelfAccessToDepartment = 1, if the same employee ID has another entry, where IsSelfAccessToDepartment = 0, in this case this line should not be retrieved.

The problem is that I want to get this information without using any subqueries and joins, because it will create performance problems since this table will have millions of records.

Answer 1

Min , 0. 1.

SELECT employeeID, MIN(IsSelfAccessToDepartment) FROM test2 GROUP BY employeeID
HAVING MIN(isSelfAccessToDepartment) = 1;

:

• , , OP
• AND MAX(isSelfAccessToDepartment) = 1, IsSelfAccessToDepartment . , , ( @Caius ).

, ( ), :

SELECT employeeID FROM test2 GROUP BY employeeID
HAVING SUM(isSelfAccessToDepartment) = COUNT(isSelfAccessToDepartment);

, isSelfAccessToDepartment 1. , , .

Answer 2

- MySQL SQLServer:

SELECT employeeID FROM table GROUP BY employeeID
HAVING MIN(IsSelfAccessToDepartment) = 1 AND MAX(IsSelfAccessToDepartment) = 1

; , . , , - . , , , 3

ROW_NUMBER() OVER(), MySQL .

, , .

Answer 3

If your DBMS is MySQL

Simple GROUP BYwith HAVINGand COUNTdoes it

SELECT Id, EmployeeID, DepartmentId, IsSelfAccessToDepartment
FROM yourtable
GROUP BY EmployeeId
HAVING IsSelfAccessToDepartment = 1
AND COUNT(EmployeeID) = 1

Output

Id  EmployeeID  DepartmentId    IsSelfAccessToDepartment
3   102         21              1

SQL Fiddle: http://sqlfiddle.com/#!9/7a75b8/1/0

If your DBMS is SQL Server

SELECT MIN(Id) AS Id, EmployeeID, MIN(DepartmentId) AS DepartmentId, MIN(IsSelfAccessToDepartment) AS IsSelfAccessToDepartment
FROM yourtable
GROUP BY EmployeeId
HAVING MIN(IsSelfAccessToDepartment) = 1
AND COUNT(EmployeeID) = 1

Output

Id  EmployeeID  DepartmentId    IsSelfAccessToDepartment
3   102         21              1

SQL Fiddle: http://sqlfiddle.com/#!6/3b4ef/4/0