QUESTION POSED ON: 09 October 2008
I am new to SQL and doing SQL by command on the AS/400. I need to make two fields in one table and uniquely match two fields in another table. If I run the following, I get exactly the records I need:
SELECT DISTINCT APPART, APSEQ# FROM METHDO WHERE APODES = 'EXTERNAL DRYING'
the result is
Part Seq
Number
BLAU1003BA 30
BLAU1006AA 30
BLAU1004BA 30
BLAU1002BA 40
but when I try to do both tables with this SELECT AOPART, AOSEQ# FROM CSTDAT/METHDR WHERE METHDR.AOPART IN ( SELECT METHDO.APPART FROM METHDO WHERE APO
DES = 'EXTERNAL DRYING ') AND
METHDR.AOSEQ# IN (SELECT METHDO.APSEQ# FROM METHDO WHERE APODES = 'EXTERNAL DRYING ') AND AODEPT = '001OS'
I get this:
Part Seq
Number
BLAU1003BA 30
BLAU1006AA 30
BLAU1004BA 30
BLAU1002BA 30
BLAU1002BA 40
Notice the BLAU1002BA occurs twice. What I believe is happening is that I am getting the part at every possible occurrence of the sequence that my condition exists.
How do I get the unique value and what would be the update statement?
|
