correlation subquery.

Posted on 1998-09-28
Medium Priority
Last Modified: 2011-10-03
I have two tables of data: Jobs and Invoices. Essentially, invoices is a subset of jobs. How can I create a query which will list the jobs that do *not* have a corresponding invoice?  
Question by:zzchoffm
  • 2

Accepted Solution

chewhoung earned 200 total points
ID: 1963742
Try this:
select * from Jobs where Jobs.JobId not in (select JobiD from Invoice)


Expert Comment

ID: 14652633
For anyone reading this thread, this is a very slow way of doing it.  You are better off doing an outer join and then adding to the clause where the Second ID Is Null.  I increased a 10 minute query to 2 seconds doing this once.


FROM Jobs LEFT JOIN Invoices ON Jobs.JobID = Invoices.JobID
WHERE (((Invoices.InvoiceID) Is Null));

or in old style SQL

FROM Jobs, Invoices
WHERE Jobs.JobID *= Invoices.JobID
AND Invoices.InvoiceID Is Null

Expert Comment

ID: 14652640
Um, duh.  I mean I DECREASED the query time, not increased. :)

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question