How to remove duplicate row in multi joined table?

1185 pts.
Tags:
AS/400
Microsoft Office 2000
SQL
My qurey is below.

SELECT [5000Phantoms79w/VNos&othersFromAna].ID, [5000Phantoms79w/VNos&othersFromAna].PhtomNo, [5000Phantoms79w/VNos&othersFromAna].[New No], [5000Phantoms79w/VNos&othersFromAna].[dB OE tied to Phantom], [5000Phantoms79w/VNos&othersFromAna].dB2ndSetNewNo, [5000Phantoms79w/VNos&othersFromAna].[dB Application], [IN0140 79 wOE].PN, [IN0140 79 wOE].ComprsOE, [IN0140 79 wOE].MSAPP, ms0802all79.PRDNO, ms0802all79.DESCP FROM ([5000Phantoms79w/VNos&othersFromAna] INNER JOIN ms0802all79 ON [5000Phantoms79w/VNos&othersFromAna].dB2ndSetNewNo = ms0802all79.PRDNO) INNER JOIN [IN0140 79 wOE] ON [5000Phantoms79w/VNos&othersFromAna].dB2ndSetNewNo = [IN0140 79 wOE].PN;

upon runing it , it returns a table that has multiple row duplicated.  I need only  one of each of  the primary keys listed.  The column ID is used as a primary key.  then there are other columns that would contain the same info too.. What I need to do is only have one row of each of the primary key numbers?  feild>>([5000Phantoms79w/VNos&othersFromAna].ID)  In the primary key column "ID"  The first column of the table each row might look like 1,1,1,1,1,4,5,6,7,7,7, and so on with multiple groups of repeated keys.  How can I only have one of each of the primary key listed

So how do I add this this query to remove duplicate rows?



Software/Hardware used:
AS400, MSxp,MS 2000 office .

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi,
I had a similar problem with row duplications.

If you want to return the first match add the keyword MIN to the ID field in the joined table and check the result or if the last one use MAX(ID)

It worked for me

Phil, I did use the word Distinct and it still gave me duplications. In your comment you me

——-
Check history for rest of discussion
phil

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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
  • carlosdl
    I would recommend to review the join conditions carefully. The reason for the duplicated rows could be there.
    68,780 pointsBadges:
    report
  • CompEng
    Carlosdl thanks for the insight. I am novice that all this for me to find that which is causing the duplications, I would not know what to look at and why? Is there any way you can explain what you mean? What is it that makes you say “I would recommend to review the join conditions carefully. The reason for the duplicated rows could be there?” Apparently you see some problems with the way the query is set up? The (5000Phantoms79w/VNos&othersFromAna) is a table. (IN0140 79 wOE) and (ms0802all79) are query from an AS400 system. I need to link the table (5000Phantoms79w/VNos&othersFromAna) with field "New No" that has my initial new numbers in the column. Then I compare that to the two AS400 queries. If I get matches with the same last four digits from (ms0802all79.PRDNO) or([IN0140 79 wOE].PN) , but numbers in front of the four digits are the same (those that came from the AS400) that are compared to (5000Phantoms79w/VNos&othersFromAna), I need to generate a new number. Which means I go back into the table. I copy the access table into a excel spread sheet. I work with the numbers excel. I then add a column into the access table and then paste the excel column into the new access with the newly generated numbers. Then I run the query again. Read the comments I add to Phil answer above and give me any insight you can. Thanks
    1,185 pointsBadges:
    report
  • carlosdl
    I didn't see anything wrong with the query, but whether it is correct or not will depend on the tables structures and the data in them. For example, if you have a table ADDRESS with these columns (among others): (address, country_id, state_id), and you have a table STATE with these columns (country_id, state_id, state_name), and you want to write a query to show the addresses with the state names. You could write a query like the following, which might look correct, but would return incorrect results (because of incorrect join conditions) if you have states for more than one country in the STATE table.
    SELECT a.address,s.state_name
    FROM address a JOIN state s
    ON a.state_id = s.state_id;
    in this case, the correct query should be:
    SELECT a.address,s.state_name
    FROM address a JOIN state s
    ON a.country_id = s.country_id
    AND a.state_id = s.state_id;
    Of course, this might not be the case in your query, but I have seen this kind of errors many times, so it is something I always recommend to review.
    68,780 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following