selecting from 2 tables w/dup keys

[grinch699]

Table 1 -
ID DATE ZIP USER_ID
111 6/1 11111 003
111 6/1 11111 708

Table 2 -
ZIP RTE DATE USER_ID
11111 1 6/1 003
11111 1 6/1 708

Key on Table 1 is ID, DATE
Key on Table 2 is ZIP, RTE, DATE

Dup key rows were inserted by mistake

I am looking to select ZIP from both tables on any instance where I have duplicate key, and USER_ID is 003 on one row, 708 in the other

 

[Guest_imported]

One solution -Delete the dup
1. IF no Ref Integrity:
- Stop the table
-Unload the table to a flat file
-delete the bad row in the flat file
-reload the table
2. IF You have Ref Integrity: (i.e. should not be the case)do the same functions in 1 for all tables.

 

[grinch699]

Thanks, but I'm strictly selecting to determine the problem ZIPs. The code that performs the initial inserts is being addressed separately.

 

[2742]

Try something like this ( i'm not sure if it's quite right for what you are trying to do)

Select count(*), id,date
From Table 1
Group by id,date
having count(*) > 1

select count(*), zip,rte,date
from table 2
group by zip,rte,date
having count(*) > 1
This will return the id and date (primary key) for every instace on the same key which has more than 1 row (the duplicates) You cound get it to return the zip code in the statement as long as in every instace the zip code is the same in the duplicates.

I'm sorry if i haven't made myself clear but i hope this helps!?

 

[MiGe]

I want to display the date of file in Approach 97

 

[Chhavindra]

Hi !!

Use the Row ID's in the table.
Delete from table1 where zipid='111' and date='6/1' and Rowid<>max(rowid).

I think this is the answer to your question.

Bye
Chhavindra