RE: SQL - Querying the Same Table

Hi Joel,

Actually, I'm not so interested in the ASNs appearing more than once,
since it will occur (An ASN will usually contain multiple part numbers).
All I care to know is if an ASN is used in both plant one AND two for
the same ASN number.  If the ASN is unique to Plant One, but is
duplicated many times, it is normal.

Thanks!

/b;

-----Original Message-----
From: Joel Cochran [mailto:joelcochran@xxxxxxxxx] 
Sent: February-15-07 2:10 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: SQL - Querying the Same Table

So really, you aren't interested in the plant number (since the
requirement
is that both plants must have a record).  In this case, you are only
interested in the ASNs that appear more than once.  It seem be
simplistic,
but Grouping and Count(*) work great for this kind of stuff:

Select ASN, Count(*) as Count
from yourtable
group by ASN
having Count(*) > 1


This is, of course, assuming that the Plant number/ASN combination are
unique.