CODEWITHSUNDEEP
sqlsql-server
geoff swartz
geoff swartz

Reputation: 5965

SQL Server query where not in another table

I have three tables:

Jobs

    JobId
    StartDate
    RehireDate

Paperwork

    PaperworkId
    DocumentName

JobPaperwork

    JobId
    PaperworkId
    Completed

I need to find any jobs where a document has NOT been assigned to it that has a certain document name. I'm not sure how to construct this query. Using NOT IN doesn't work because it returns every job since it's finding jobs with other paperwork assigned to them.

Here's what I started with:

select j.jobid 
from Job j 
inner join JobPaperwork jp on j.JobId = jp.JobID 
where j.startdate > dateadd(day, -30, getdate()) 
  and j.rehiredate is not null 
  and jp.PaperworkID not in (select paperworkid 
                             from Paperwork 
                             where documentname like '%searchterm%')

Upvotes: 1

Views: 67

Answers (2)

Paulo Roberto Elias
Paulo Roberto Elias

Reputation: 9

You can use NOT EXISTS statement, like this:

select j.jobid 
from Job j 
inner join JobPaperwork jp on j.JobId = jp.JobID 
where j.startdate > dateadd(day, -30, getdate()) 
and j.rehiredate is not null 
and jp.PaperworkID not exists (
select 1 
from Paperwork 
where 
documentname like '%searchterm%'
)

Upvotes: -1

M.Ali
M.Ali

Reputation: 69494

select j.jobid 
from Job j 
inner join JobPaperwork jp on j.JobId = jp.JobID 
where j.startdate > dateadd(day, -30, getdate()) 
and j.rehiredate is not null 
and NOT EXISTS ( select 1 
                 from Paperwork pw 
                 where jp.PaperworkID = pw.paperworkid
                 and pw.documentname like '%searchterm%'
                )

Upvotes: 5

Related Questions