Oracle SQL Filter common registries (1:1) between two tables-CodePudding

I need some help to approach a proper counting between 2 tables.

There are 2 tables with duplicated fields on each table. I need to filter same registries between these 2 tables but it must be counted only once per ID on both tables.

Table 1                                 Table 2

| ID | MSISDN | DATA |                  | ID | LINE   | BYTES | 
----------------------                  ----------------------
| 11 | 123    | 12   |                  | 1  | 123    | 12    |
| 22 | 123    | 12   |                  | 2  | 123    | 12    |
| 33 | 123    | 12   |                  | 3  | 456    | 56    |
| 44 | 456    | 56   |                  | 4  | 456    | 56    |
| 55 | 456    | 56   |                  | 5  | 456    | 56    |
| 66 | 183    | 15   |                  | 6  | 141    | 71    |

I was thinking about selecting by Distinct ID but still not getting the correct result.

select *
from tbl1 A
inner join tbl2 B
on A.msisdn = B.line and A.data = B.bytes

The correct result should show only 4 rows:

| MSISDN | DATA | ID_A | ID_B |
-------------------------------
| 123    | 12   | 11   | 1    |
| 123    | 12   | 22   | 2    |
| 456    | 56   | 44   | 3    |
| 456    | 56   | 55   | 4    |

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=fb9cec42162a6a3a9bd3e1bea586ce99

CodePudding user response：

Looks like you want a position matching

select A.*, B.*
from (
  select tbl1.*, row_number() over(partition by msisdn, data order by id) pos
  from tbl1) A
inner join (
  select tbl2.*, row_number() over(partition by line, bytes order by  id) pos
  from tbl2) B
on A.msisdn = B.line and A.data = B.bytes and A.pos = B.pos