Show only in progress records without finalized ones-CodePudding

I have the following table CandidateInterview :

CandidateID	InterviewID	Status
100	12	Not yet
100	13	In progress

CREATE TABLE CandidateInterview (CandidateID int, InterviewID int,
Status int)
INSERT INTO CandidateInterview VALUES 
(100     ,    12     ,     1),
(100      ,   13     ,     2),
(120     ,     9      ,    1)

I want to get only the candidates with only with Not Yet status and not other ones

CodePudding user response：

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE CandidateInterview (CandidateID int, InterviewID int,
Status int)
INSERT INTO CandidateInterview VALUES 
(100     ,    12     ,     1),
(100      ,   13     ,     2),
(120     ,     9      ,    1)

Query 1:

SELECT * FROM CandidateInterview AS A
WHERE NOT EXISTS 

(
  SELECT * FROM CandidateInterview AS B
  WHERE A.CandidateID=B.CandidateID
  AND Status >1
  
  
  )

Results:

| CandidateID | InterviewID | Status |
|-------------|-------------|--------|
|         120 |           9 |      1 |

CodePudding user response：

You can use window functions for this.

Window functions are normally more efficient than correlated subqueries

SELECT
  CandidateID,
  InterviewID,
  Status
FROM (
    SELECT *,
      OtherStatus = COUNT(CASE WHEN Status > 1 THEN 1 END) OVER (PARTITION BY CandidateID)
    FROM CandidateInterview ci
) ci
WHERE OtherStatus = 0;

SQL Fiddle