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!
CodePudding user response：
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);