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
Advertisement
Answer
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