Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Check 2 tables by 2 fields 2

Status
Not open for further replies.

Andrzejek

Programmer
Jan 10, 2006
8,529
US
If I have some ‘unrelated’ tables (no PK no FK relation between tables), I can check if I have some IDs in one table that are NOT in the other table by:

[pre]
SELECT B.ID
FROM SomeTable B
WHERE (NOT (B.ID IN
(SELECT A.ID FROM OtherTable A)))[/pre]

And that works fine.

Now, if I want to check if there are 2 fields that should match between those 2 tables, I tried:

[pre]
SELECT A.ID || '*' || A.PN
FROM SomeTable A
WHERE (NOT ((A.ID || '*' || A.PN)
IN (SELECT B.ID || '*' || B.PN FROM OtherTable B)))[/pre]

Which is pretty much: concatenate ID and PN (with * between) in both tables and find ‘missing’ records.

That also works, but it takes a long time to process.

Is there any other, better, faster, more elegant way of checking tables by 2 (or more?) fields?


Have fun.

---- Andy
 
Yes: Create a temporary index.
[3eyes]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
. . . and use NOT EXISTS ( ) . . .

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Thank you LKBrwnDBA, but I cannot create temporary indexes on the tables. :-( I don’t really ‘own’ the tables, I just have to work with them (without making any modifications/changes/additions etc. even temporary). All what I want to do is to find those ‘orphan’ records. And since the tables are joint on 2 or more fields, I am trying to find the most efficient way to do that.
Right now my attempt takes about 15 sec to compare 2 tables, and I may have many pair of tables to compare.


Have fun.

---- Andy
 
How big the tables?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
PS: You could create indexes on your own work schema...

And also/or then:

a) Use: NOT EXISTS...

b) If the tables are small, load the compare columns into pl/sql tables and compare simulating anti-join.

Code:
SELECT A.Id, A.Pn
  FROM Sometable A
 WHERE NOT EXISTS
          (SELECT '?'
             FROM Othertable B
            WHERE B.Id = A.Id
              AND B.Pn = A.Pn);
[noevil]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Thank you LKBrwnDBA
This works a LOT faster :)

[pre]
SELECT A.Id, A.Pn
FROM Sometable A
WHERE NOT EXISTS
(SELECT '?'
FROM Othertable B
WHERE B.Id = A.Id
AND B.Pn = A.Pn); [/pre]

Have fun.

---- Andy
 
You might also want to try
Code:
SELECT Id, Pn FROM tableA
MINUS
SELECT Id, Pn FROM tableB;
This will typically run faster than using a subquery.

As for checking to see if there are matching fields, you could try a plain old inner join:
Code:
SELECT a.id, a.pn
FROM tableA AS a
     INNER JOIN tableB AS b
     ON a.id = b.id AND a.pn = b.pn;
This is not only easier to read, but should also be faster than using a subquery.
 
Ooops - just noticed you wanted the records in B that aren't in A. So the code above should be:
Code:
SELECT Id, Pn FROM tableB
MINUS
SELECT Id, Pn FROM tableA;
 
Thank you carp,
I totally forgot about the MINUS option :)
That also gives me the easy way for checking on more than just 2 filelds.

Have fun.

---- Andy
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top