Ask the Expert

Uniquely matching two fields in an SQL table to fields in a different table

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?

Here's an example of the UPDATE statement that you're trying to run:

UPDATE methdo SET apodes = 
(SELECT R.apodes FROM methdo O INNER JOIN methdr R 
    O.AOPART = R.AOPART and O.AOSEQ# = R.AOSEQ#) 
WHERE EXISTS 
(SELECT 1 FROM methdr R2 
    methdo.AOPART = R2.AOPART and methdo.AOSEQ# = R2.AOSEQ#) 

This was first published in January 2009

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: