• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • 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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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