Re: SQL - Querying the Same Table

Brian - How about this:

select distinct aa.asn from temp1 aa
left outer join temp1 bb on
aa.asn = bb.asn
where aa.plant <> bb.plant

- sjl

To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, February 15, 2007 1:36 PM
Subject: 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.

--
Joel Cochran



On 2/15/07, Brian Piotrowski <bpiotrowski@xxxxxxxxxxxxxxx> wrote:

Hi All,



Can someone suggest the best way to get at this data via an SQL
statement?



I have a table that has two specific fields in which I am interested.
One field is a plant code and the other field is an ASN field.  I want
to run an SQL statement that will show me all the ASNs that are used

by

both plants.  So if the table has:



1, 10200

1, 10201

2, 30210

2, 23041

1, 30210



I would like to run a statement that will show me:



30210



This should be the only ASN to appear since it is used by both Plant

One

and Plant Two.



Any ideas?  I'm pretty sure it's blatantly obvious, but I'm having a

bit

of an issue trying to get the statement correct.



Thanks!



/b;

-=-=-=-=-=-=-=-=-=-=-=-=-=-
Brian Piotrowski
Assisstant Mgr. - I.T.
Simcoe Parts Service, Inc.
Ph: 705-435-7814 x343
Fx: 705-435-6746
-=-=-=-=-=-=-=-=-=-=-=-=-=-



--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.