• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

correlation subquery.

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?  
0
zzchoffm
Asked:
zzchoffm
  • 2
1 Solution
 
chewhoungCommented:
Try this:
select * from Jobs where Jobs.JobId not in (select JobiD from Invoice)

0
 
dtburdickCommented:
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.

Ie.

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

or in old style SQL

SELECT Jobs.*
FROM Jobs, Invoices
WHERE Jobs.JobID *= Invoices.JobID
AND Invoices.InvoiceID Is Null
0
 
dtburdickCommented:
Um, duh.  I mean I DECREASED the query time, not increased. :)
0
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now