I'm trying to join two tables and keep records from both. Some of the record id/loan numbers are in both tables but some are not. I'm ending up with duplicates using an outer join. Is there a way to prevent this?
More clarification may be needed. The 1st table is a record of all current loans (no [Old Loan Number] records exists in this table). The 2nd table contains loans paid off [Old Loan Number] as well as those where the customer was recaptured with a new loan (indicated by [Loan Number] of the new loan). The 2nd table new [Loan Number] may be contained in the 1st table, but may not due to timing issues. I want to keep one record for each [Loan Number] that exists in either table. Hope that helps.
For me isn't it quite clear yet how your sources look like and what should be the expected result by merging them. It could be that it won't be possible with an one-step solution - maybe you need further load steps to flag and remove the unwanted records, for example with a check to the previous record in a sorted load. Take a look here what I mean: Peek() or Previous() ?
I believe you are right about a 2 step process. So table 1 will have fields
Table 2 will have fields
New Loan Number
Old Loan Number
When I perform a simple outer join I get the results in the initial post in this thread. I'd like to include all of the records from the 1st table all of the records with an Old Loan Number from the second table, matching on any records where the Loan Number = New Loan Number