SQL Server Select line where jobCategoryKey EXISTS once for one candidateKey
All the proposed solutions using COUNT may prove to be inefficient as the aggregates will most likely need to be computed for the entire set, unless the optimizer will be able to push the predicate down. There is a more natural solution which IMHO is much clearer, and typically more efficient. It involves wording the question in negative form: 


  "Show me all candidates for category 1245, who have no other
  association with any other category".


This translates to SQL immediately:

SELECT *
FROM Candidates_to_Jobs AS C1
WHERE jobCategoryKey = 1245
      AND 
      NOT EXISTS (
                  SELECT NULL
                  FROM Candidates_to_Jobs AS C2
                  WHERE C1.CandidateKey = C2.CandidateKey
                        AND C2.jobCategoryKey &lt;&gt; 1245
                 );