CODEWITHSUNDEEP
sqlsql-server
fault_final
fault_final

Reputation: 23

How to join tables in sql to exclude rows already matched from further consideration

I have 2 tables as shown below. I am trying to join table 1 to table 2 on order_code such that once a row matches, the corresponding id from either table should not show up again on the joined table. The match should happen in ascending order of date from both tables. Expected results are shown as well:

Table 1:

PK1 order_code Date1
1 XYZ 1/1/2021
2 ABC 1/2/2021
3 ABC 1/3/2021
4 XYZ 1/4/2021

Table 2:

PK2 order_code Date2
1 ABC 2/7/2021
2 XYZ 2/6/2021
3 ABC 2/5/2021
4 XYZ 2/8/2021
5 ABC 2/11/2021
6 XYZ 2/14/2021

Expected result:

PK1 order_code Date1 PK2 order_code Date2
1 XYZ 1/1/2021 2 XYZ 2/6/2021
2 ABC 1/2/2021 3 ABC 2/5/2021
3 ABC 1/3/2021 1 ABC 2/7/2021
4 XYZ 1/4/2021 4 XYZ 2/8/2021

Please let me know if more clarity is needed and I can try explaining this better. All help is appreciated!

Upvotes: 0

Views: 259

Answers (1)

Serg
Serg

Reputation: 22811

Join by order_code and a row position within an order_code.

select t1.PK PK1, t1.order_code, t1.Date Date1, t2.PK PK2, t2.order_code, t2.Date Date2
from (
   select *, row_number() over(partition by order_code order by Date) rn
   from table1
) t1
join (
   select *, row_number() over(partition by order_code order by Date) rn
   from table2
) t2 on t1.order_code = t2.order_code and t1.rn = t2.rn);

Upvotes: 1

Related Questions