INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Check 2 tables by 2 fields

Check 2 tables by 2 fields

(OP)
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:

SELECT B.ID
FROM SomeTable B
WHERE (NOT (B.ID IN
      (SELECT A.ID FROM OtherTable A))) 

And that works fine.

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

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

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

RE: Check 2 tables by 2 fields

Yes: Create a temporary index.
3eyes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Check 2 tables by 2 fields

. . . and use NOT EXISTS ( ) . . .

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Check 2 tables by 2 fields

(OP)
Thank you LKBrwnDBA, but I cannot create temporary indexes on the tables. sad 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

RE: Check 2 tables by 2 fields

How big the tables?

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Check 2 tables by 2 fields

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

RE: Check 2 tables by 2 fields

(OP)
Thank you LKBrwnDBA
This works a LOT faster smile

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);  

Have fun.

---- Andy

RE: Check 2 tables by 2 fields

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.

RE: Check 2 tables by 2 fields

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; 

RE: Check 2 tables by 2 fields

(OP)
Thank you carp,
I totally forgot about the MINUS option smile
That also gives me the easy way for checking on more than just 2 filelds.

Have fun.

---- Andy

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close