CODEWITHSUNDEEP
sqloracle-database
Nadavdan
Nadavdan

Reputation: 11

Get missing rows from two tables without matching indexes

I'm trying to find out the correct sql but without any luck. I've two tables:

TBL1
    code         varchar  pk
    parent_code  varchar
    row          integer  pk
    parent_row   integer
    desc         varchar
    order_number integer  pk
    version      integer

TBL2
    code         varchar  pk
    parent_code  varchar
    row          integer  pk
    parent_row   integer
    desc         varchar
    order_number integer  pk
    version      integer  pk

TBL1 is the master table. Every time something changes, the version increase by 1. On TBL2 I've all the versions saved so that I can catch the changes. I cannot touch TBL1 cause data comes from outside, but I can change whatever I want on TBL2. I want to get rows that are added on the current version (TBL1) but are missing from the previous one (on TBL2). The problem is that row and parent_row, that could be have easily been the pk cause they are progressive, can change between versions, so I could have the following scenario:

TBL1
AA01   AA00   2    1    example 2  110    5
AA02   AA00   3    1    example 3  110    5
AA03   AA00   4    1    example 4  110    5
AA04   AA01   5    2    example 5  110    5

TBL2
AA01   AA00   2    1    example 2  110    4
AA02   AA00   3    1    example 3  110    4
AA04   AA01   4    2    example 5  110    4
AA01   AA00   2    1    example 2  110    3
AA02   AA00   3    1    example 3  110    3
AA04   AA01   4    2    example 5  110    3

I want to find out that code AA03 was added in the last version if I compare with the previous one. I was trying with a LEFT JOIN, but column number 4 refers to two different articles if compared between TBL1 AND TBL2, so I cannot use it on the join query.

SELECT
    A.CODE,
    A.PARENT_CODE,
    A.ROW,
    A.PARENT_ROW,
    B.CODE
FROM TBL1 A
    LEFT JOIN TBL2 B ON A.CODE = B.CODE AND A.PARENT_CODE = B.PARENT_CODE
WHERE A.ORDER = 110
    AND (B.VERSION = 4 OR B.VERSION IS NULL)

But with this sql I'm not getting the null on column B.CODE

The problem is that I could have multiple codes (they are not unique), even under the same parent code.

Is there a way to find the rows added in the lates verion, like in this example line 4 of TBL1 with code AA03 ? I'm using ORACLE 11

Thank you

Upvotes: 0

Views: 63

Answers (1)

Yaron Idan
Yaron Idan

Reputation: 6765

If all you are looking for are the codes that appear in TBL1 and not in TBL2 (as in the values in column CODE) - join is not the right thing to use. You should perform a minus between the two tables, and the result would be a list of codes that doesn't exist in TBL2, like so - 

select code from tbl1
minus
select code from tbl2;

Upvotes: 1

Related Questions