Re: missing master records
Charles Perez (perez@mgr.hjf.org) wrote:
> In article <321ofb$q8n@emory.mathcs.emory.edu> tilh@sin-co.sin-ro.DHL.COM (Ti Lian Hwang) writes:
> >Anybody out there knows how to select records from one table (table 1)
> >which has a common key with another table (Table 2), and where the record
> >exists in table 1 but not in table 2 ?
> >Eg. I've got a master invoice table "invmst", and a invoice line items table
> >"invlin". They have a common key "invoice". I want to find records in the
> >"invlin" table which has no corresponding "invoice" number in the "invmst"
> >table.
> >I've seen the solution somewhere, using a nested select, but I have not
> >been able to dig it up since.
> The obvious way is to use a subquery. You'll see several examples on this
> thread. If performance is a problem, however, you may want to do it in two
> steps:
> SELECT invmst.*, invlin.invoice inv_invoice
> FROM invmst, outer invlin
> WHERE (invmst.invoice = invlin.invoice
> INTO TEMP lone_invoices
> <create whatever indices on lone_invoices would make things efficient>
> SELECT <whatever you need>
> FROM lone_invoices
> WHERE (inv_invoice IS NULL)
> When you get to serious row counts, subqueries are usually a BEAR. Doing the
> above could make a 4-hour query take 15 minutes or less. At least, it's
> worked that way for me.
> --
> ==========================================================================
> You can't win. -- Murphy
> You can't break even. -- The Second Law of Thermodynamics
> You can't quit the game. -- The Buddha
Try:
SELECT * FROM invlin
WHERE invlin.invoice NOT IN
(SELECT invest.invoice FROM invest)
Danni