1

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%')
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
geoff swartz
  • 5,437
  • 11
  • 51
  • 75
  • 1
    it should be possible with NOT IN or NOT EXISTS - if you're looking for a document with a specific name then it shouldn't return paperwork which isn't that document. Show us your NOT IN attempt as well. – ADyson Aug 03 '18 at 13:01

2 Answers2

5
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%'
                )
M.Ali
  • 67,945
  • 13
  • 101
  • 127
-1

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%'
)
Paulo Roberto Elias
  • 9
  • 3
  • `and jp.PaperworkID not exists (` would be a syntax error and the `not exists` doesn't filter to the specific case in the query. – Zorkolot Aug 03 '18 at 13:13