>> When no matching record exist in the PROCESS table, the values are .NULL..
That's what I was missing !!!! Thanks, Francis -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tracy Pearson Sent: Friday, December 05, 2008 2:07 PM To: [EMAIL PROTECTED] Subject: RE: SQL Query JOIN clauses work with ON clauses to get better control of what how the data is being joined. This LEFT JOIN forces all records from the POL table. When no matching record exist in the PROCESS table, the values are .NULL.. Tracy -----Original Message----- From: Francis I. Coppage, Jr. Sent: Friday, December 05, 2008 2:56 PM Great Tracy ... works like a charm I got most of it fused in grey-matter except the ISNULL() and ???. How does the process.orderno end up being NULL and it return the correct lineitem/sequence. I tried something similar (trashed it when it didn't work) and got a mish-mash of lineitem's from other orderno's associated to the wrong orderno. Would you mind? Thanks, Francis -----Original Message----- From: Tracy Pearson Sent: Friday, December 05, 2008 1:29 PM Perhaps this: Select po.orderno, pol.lineitem ; From po inner join pol on po.orderno == pol.orderno ; Where po.orderno not in ; (select pol.orderno from pol left join process ; On pol.lineitem == process.sequence ; Where isnull(process.orderno)) -----Original Message----- From: Francis I. Coppage, Jr. Sent: Friday, December 05, 2008 1:41 PM I know I've done this before, but right now I'm suffering from a Brain F... reeze or something. I have a PO file, a PO Lineitem file, and a Process file. PO Orderno PO Lineitem Orderno LineItem Process Orderno Sequence (same as LineItem) What I need to do is retrieve all PO's that do not have a corresponding Orderno AND Sequence in Process. It's quite obvious if I just wanted to eliminate PO's that have a Process.Orderno SELECT po.orderno, pol.lineitem, .... FROM po, pol WHERE po.orderno = pol.orderno AND po.orderno NOT IN (SELECT orderno FROM process) BUT There may be a Process for one or more of the POLineItems but not all. If all of the POLineItems do not have an associated Process I need to Retrieve the PO, POL information in order to create a new Process for missing lineitem process. Any takers??? Thanks for the look, Francis [excessive quoting removed by server] _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.