Re: missing master records
Hey, I think I know this one!<BigGrin>
select all from tab1 where
invoicenum not in ( select invoicenum from tab2 ) ;
Hope that helps.
Ray
///////////////////////////////////////////////////////////////////
// Ray Storer; SGT, US Army; Software Engineer, TM LDR //
// E-mail: eapc-amo2@emh7.korea.army.mil //
// US Mail: HHC, 8TH PERSCOM;UNIT: 15316 ATTN: IMO;APO AP 96205 //
// Voice/FAX: 011-82-2-7914-3359 / 6730 //
// * Disclaimer: Opinions expressed are mine and do not, //
// * in any way, reflect US Gov., Army, DOD or anyone else's //
// * opinions or policies. //
///////////////////////////////////////////////////////////////////
>Date: Sun, 7 Aug 94 12:29:19 +0800
>From: Ti Lian Hwang <tilh@sin-co.sin-ro.DHL.COM>
>To: informix-list@rmy.emory.edu
>Subject: missing master records
>
>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.
>
>