Re: missing master records

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