How to remove duplicate row in multi joined table?
Home > IT Answers > Development > How to remove duplicate row in multi joined t...
CompEng
600 pts.
0
Q:
How to remove duplicate row in multi joined table?
SQL, AS/400, Microsoft Office 2000
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 .
ASKED: Sep 22 2009  6:08 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Philpl1jb
24530 pts.
0
A:
 RATE THIS ANSWER
+3
Click to Vote:
  •   3
  •  0
  • AddThis Social Bookmark Button
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
Last Answered: Sep 29 2009  6:49 PM GMT by Philpl1jb   24530 pts.
Latest Contributors: CompEng   600 pts., MBell   40 pts., PS2112   30 pts., Meandyou   1840 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Carlosdl   29770 pts.  |   Sep 22 2009  7:24PM GMT

I would recommend to review the join conditions carefully. The reason for the duplicated rows could be there.

 

CompEng   600 pts.  |   Sep 23 2009  12:33PM GMT

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

 

Carlosdl   29770 pts.  |   Sep 23 2009  2:18PM GMT

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.

 
0