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
>Solution :
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