How can a select match each value only once?

Question

I'm matching amount values from table1 and table2, tables having duplicates and different number of rows. Any amount can be matched only once (so not the way a standard select works) because total amount in the select for a table should remain the same as if I added all inserted values from that table.

CREATE TABLE table1 (
    table1_amount int
);
INSERT INTO table1 (table1_amount)
VALUES
    (22),
    (11),
    (35),
    (45),
    (45),
    (65),
    (22),
    (22),
    (878),
    (56);

CREATE TABLE table2 (
    table2_amount int
);
INSERT INTO table2 (table2_amount)
VALUES
    (324),
    (43),
    (34),
    (35),
    (22),
    (22),
    (12),
    (35),
    (6);

A standard select would return 6 rows matching the three "22" from table1 to the two "22" from table2 (so every "22" from table1 gets matched twice):

SELECT table1.table1_amount, table2.table2_amount
FROM table1 FULL OUTER JOIN table2 ON table1.table1_amount=table2.table2_amount;

table1_amount table2_amount
22 22
22 22
22 22
22 22
22 22

Now, I'd like to have only 2 matchings + 1 unmatched amount from table1 so:

table1_amount table2_amount
22 22
22 22
22 NULL

How can you do that? I'm indicating here SQL but any solution (Excel, Access) would be good.

I think it will be difficult to generate your exact expected output based on the two tables you currently have. But, do you really need this output? Or, would you be OK with a report showing discrepancies in number of occurrences? — Tim Biegeleisen, Jul 06 '18 at 06:19

score 0 · Accepted Answer · answered Jul 06 '18 at 06:25

0

Try this using Row_Number():

with cte1 as
(Select *, ROW_NUMBER() over (partition by table1_amount order by table1_amount) as ranking from table1),
 cte2 as
(Select *,ROW_NUMBER() over (partition by table2_amount order by table2_amount) as ranking from table2)
Select cte1.table1_amount, cte2.table2_amount from cte1 FULL OUTER JOIN cte2 on cte1.table1_amount = cte2.table2_amount and cte1.ranking = cte2.ranking

answered Jul 06 '18 at 06:25

Ajay Gupta

1,775
1
10
22

Works just fine as an SQL query - thanks a lot! Hmm, I'm trying to figure out how to use that query in MS Access, which does not support FULL OUTER JOIN -- any idea? – Krzysztof Tomasz Straszak Jul 06 '18 at 07:21
I have no experience in Access but [This](https://stackoverflow.com/q/19615177/9676724) might help – Ajay Gupta Jul 06 '18 at 07:32
Thanks -- Also, Access doesn't support the WITH keyword... So I keep on digging :) – Krzysztof Tomasz Straszak Jul 06 '18 at 08:00

score 0 · Answer 2 · answered Jul 06 '18 at 09:52

0

Try this:

select cte1.table1_amount, cte2.table2_amount from (Select *, ROW_NUMBER() over (partition by table1_amount order by table1_amount) as ranking from @table1) as cte1 left join (Select *,ROW_NUMBER() over (partition by table2_amount order by table2_amount) as ranking from @table2) as cte2 on cte1.table1_amount = cte2.table2_amount and cte1.ranking = cte2.ranking

answered Jul 06 '18 at 09:52

Om.

113
14

Access signals "a syntax error, missing operator" here: `ROW_NUMBER() over (partition by table1_amount order by table1_amount) as ranking from @table1)` – Krzysztof Tomasz Straszak Jul 06 '18 at 22:52

How can a select match each value only once?

2 Answers2