EXCLUDE query? (1 Viewer)

[dlambert]

I have the following:

tblJobs
qryJobsWithMaterials

both above have a field called JobID

I need a query (to be called qryJobsWithoutMaterials) that outputs all the JobIDs from tblJobs EXCEPT the ones listed in qryJobsWithMaterials

Been going around in circles for hours, not finding a solution to something that is probably really simple...?

Any guidance would be much appreciated

 

[Josef P.]

3 variants:
1) left join + is null check

select
     ...
from
     tblJobs as J
     left join
     qryJobsWithMaterials as M ON M.JobId = J.JobID
where
    M.JobId IS NULL

2) not exists

select
     ...
from
     tblJobs as J
where
     not exists (select 1 from qryJobsWithMaterials as M where M.JobId = J.JobID)

3) invert logic of qryJobsWithMaterials

Which of the 3 variants is the best depends on the data.

see also: isladogs.co.uk: Speed Comparison Tests - Finding Unmatched Records

 

[dlambert]

3 variants:
1) left join + is null check

select
     ...
from
     tblJobs as J
     left join
     qryJobsWithMaterials as M ON M.JobId = J.JobID
where
    M.JobId IS NULL

2) not exists

select
     ...
from
     tblJobs as J
where
     not exists (select 1 from qryJobsWithMaterials as M where M.JobId = J.JobID)

3) invert logic of qryJobsWithMaterials

Which of the 3 variants is the best depends on the data.

see also: isladogs.co.uk: Speed Comparison Tests - Finding Unmatched Records

Thankyou!!!
I used option 2 and it worked perfectly for me. (that option was close to what i was unsuccessfully trying to do before i posted the question)
(amazing i barely had time for a short meeting and had your response when i got back to my computer!! )

 

[Josef P.]

Variant 3 could possibly be more efficient.

If query qryJobsWithMaterials looks like this:

select
...
from
   tblJobs as J
where
   exists (select 1 from MaterialsForJobTable X where X.JobID = J.JobID)

=> qryJobsWithoutMaterials:

select
...
from
   tblJobs as J
where
   not exists (select 1 from MaterialsForJobTable X where X.JobID = J.JobID)

...variant 'left join with is null' could be faster with an Access BE (with few material data per job).