How do I link two values in a nested SQL SELECT query?

Question

I have an SQL Server table JobFiles with columns JobId and FileId. This table maps which file belongs to which job. Each job can "contain" one or more files and each file can be "contained" in one or more job. For every pair such that job M contains file N there's a row (M,N) in the table.

I start with a job id and I need to get a list of all files such that they belong to that job only. I have hard time writing a request for that. So far I've crafted the following (pseudocode):

SELECT FileId FROM JobFiles WHERE JobId=JobIdICareAbout AND NOT EXISTS
    (SELECT * FROM JobFiles WHERE FileId=ThatSameFileId AND JobId<>JobIdICareAbout);

The above I believe would work but I have a problem of how to map ThatSameFileId onto the FileId returned from the outer SELECT so that the database knows that those are the same.

How do I do that? How do I tell the database that FileId in the outer SELECT must be equal to the FileId in the inner SELECT?

Answer 1

How about using NOT IN:

SELECT FileId FROM JobFiles WHERE JobId=JobIdICareAbout AND FileID NOT IN
    (SELECT FileId FROM JobFiles WHERE JobId<>JobIdICareAbout);

And a slight variation on that:

SELECT FileId FROM JobFiles WHERE JobId=JobIdICareAbout
EXCEPT
SELECT FileId FROM JobFiles WHERE JobId<>JobIdICareAbout