Joining Returns Duplicate Rows

Question

I'm having problems figuring out how to reconcile records against two tables. Table 1 will contain records from one system and Table 2 will contain records from another system. Both tables will have an ID column unique to itself. It's possible that Table 1 will contain similar records, but with a different ID and the same for Table 2.

Table 1

ID       | Acct_Num  |  Amount | Dt
---------+-----------+---------+-------------
96       | 5836      | 75      | 2020-04-02
100      | 5836      | 75      | 2020-04-02

Table 2

ID       | Acct_Num  |  Amount | Dt
---------+-----------+---------+-------------
3        | 5836      | 75      | 2020-04-02
39       | 5836      | 75      | 2020-04-03

When I try to join on Acct_Num and Amount, the result returns 4 records, both records in Table 1 matching to both records in Table2.

SELECT * FROM Table1 t1 INNER JOIN Table 2 ON t1.Acct_Num = t2.Acct_Num AND t1.Amount = t2.Amount

ID       | Acct_Num  |  Amount | Dt          |  ID       | Acct_Num  |  Amount | Dt
---------+-----------+---------+-------------+-----------+-----------+---------+-------------
96       | 5836      | 75      | 2020-04-02  |  3        | 5836      | 75      | 2020-04-02
96       | 5836      | 75      | 2020-04-02  |  39       | 5836      | 75      | 2020-04-03
100      | 5836      | 75      | 2020-04-02  |  3        | 5836      | 75      | 2020-04-02
100      | 5836      | 75      | 2020-04-02  |  39       | 5836      | 75      | 2020-04-03

I understand this is how joins work, but what I'm looking to accomplish is to have a record on the left to match with just one record on the right. I don't care which one. The next record on the left will then match against the next available record on the right. Ending result as:

ID       | Acct_Num  |  Amount | Dt          |  ID       | Acct_Num  |  Amount | Dt
---------+-----------+---------+-------------+-----------+-----------+---------+-------------
96       | 5836      | 75      | 2020-04-02  |  3        | 5836      | 75      | 2020-04-02
100      | 5836      | 75      | 2020-04-02  |  39       | 5836      | 75      | 2020-04-03

I'm a bit lost on how I could accomplish this. Any suggestion would be helpful!

What would be the output if, for example, the first (or second) table had _three_ records instead of just two? — Tim Biegeleisen, Jun 23 '20 at 03:52
Give a clear complete description of what rows you want back in terms of inputs. When pinned down this will be a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 23 '20 at 03:54
@TimBiegeleisen, good question. If the first table had 3 records, the 3rd record (numbering based on ID), would not match on anything against the second table. If the second table had 3 records, it would not match on anything against the first table. — jeefreak, Jun 23 '20 at 03:54
[Select first row in each GROUP BY group?](https://stackoverflow.com/q/3800551/3404097) — philipxy, Jun 23 '20 at 03:57

Tim Biegeleisen · Accepted Answer · 2020-06-23T04:03:23.533

If you really don't care how the records get paired up, we could try doing a full outer join using ROW_NUMBER ordered by the ID column:

WITH cte1 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Acct_Num ORDER BY ID) rn
    FROM Table1
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Acct_Num ORDER BY ID) rn
    FROM Table2
)

SELECT t1.ID, t1.Acct_Num, t1.Amount, t1.Dt, t2.ID, t2.Acct_Num, t2.Amount, t2.Dt
FROM cte1 t1
FULL OUTER JOIN cte2 t2
    ON t1.Acct_Num = t2.Acct_Num AND
       t1.rn = t2.rn
ORDER BY
    t1.Acct_Num,
    t1.ID;

Joining Returns Duplicate Rows

1 Answers1

Demo