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
_______________________________________________
Post Messages to: [email protected]
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.
