SQL: How to display one record from a file without affecting the others

Hi list!

Another SQL challenge (at least for me): I have an SQL SELECT Statement
with 3 files. I need to add a 4th one (REQMTS); however, in this file I
may have more than one record that will match the selection criteria. I
just want to have one record and not all of the records that match the
selection criteria.

For example, I have a component item called ABC123 and it may be used in
more than one finished item, let's day 7777 and 7777A. It will look
something like this:

Component Warehouse Finished
Item Id Quantity Item
ABC123 N1 10 7777
ABC123 N1 15 7777
ABC123 N1 25 7777A
ABC123 N1 7 7777A
ABC123 N1 13 7777A

What I want is (by using the component item and the warehouse id) to pull
in the FINISHED ITEM that is most often associated with a component item
(in this case, 7777A) and the total QUANTITY for that FINISHED ITEM (in
this case, 45 (25 + 7 + 13)). In addition, I want to include the file
and this new logic in this SQL Statement (that is currently running):
SELECT I.HOUSE, I.ITNBR, I.PLANIB,
R.ITCLS, R.ITDSC, R.ENGNO, R.ITTYP,
P.PODUD, P.POSTD, P.POQTY, P.PORID,
I.ACREC

FROM
ITEMBL I, ITMRVA R, PLNORD P

WHERE
I.ITNBR = P.POITM AND
I.HOUSE = P.POPLWH AND
I.ITNBR = R.ITNBR AND
I.ACREC = 'S' AND
R.STID = 'N1P' AND
(I.HOUSE LIKE 'N%')

Any ideas? Any help is greatly appreciated.


PETER VIDAL
PALL CORPORATION | SR SYSTEM ANALYST @ WH APPLICATION DEVELOPMENT GROUP
10540 RIDGE RD., SUITE 203, NEW PORT RICHEY, FL 34654-5111
727-815-3104 ||| FAX: 727-815-3120 ||| WWW.PALL.COM

"Imagination is more important than knowledge..."
Albert Einstein (1879 - 1955)

--------------------------------------------------------------------------------

Attention:

This communication may contain information that is confidential,
privileged and/or exempt from disclosure under applicable law.
If you are not the intended recipient, please notify the sender
immediately and delete the original, all attachments, and all
copies of this communication.


--------------------------------------------------------------------------------