Matching Records Using LIKE or MATCH Condition

jc2638 · #1 (**permalink**) 06-06-08, 17:01

Here is the sample data

Table A

Bank ID|Acct_number
ID123|2245,3456,002245,1234
ID123|77778900000000
ID123|112244
ID123|4455,6666,0004455

Table B

Bank ID|Acct_number
ID123|2245-- This Should not be in the missing number file
ID123|777789 -- This Should not be in the missing number file
ID123|112244-- This Should not be in the missing number file
ID123|6666--This should be in the missing file
ID123|888889--This should be in the missing file
ID123|667788--This should be in the missing file

I am trying to find missing account numbers in table B that are not in table A using the query below, but it's not giving me the missing results.

If you look at the data in table A there are multiple account numbers, and also leading zeros

unload to missing_acct_numbers1.txt
select tempb.di_id, tempb.acct_number from tempb ,tempa where
tempb.di_id = tempa.di_id and
tempb.acct_number = tempa.acct_number
and tempb.acct_number NOTLIKE '%'||tempa.acct_number||'%'};

Any suggestions will be helpful

Thanks a lot in advance.

ceinma · #2 (**permalink**) 06-12-08, 20:18

this is never will work...
how do you can get a data from a SQL when they don`t exists...

If you put tempa as inner join , you will have only what are in common between tables. And with the last clause you just invert all logical , bring nothing...

Try this... maybe need some adjusts..

Code:

unload to missing_acct_numbers1.txt
select tempb.di_id, tempb.acct_number 
from tempb 
where
 not exists ( 
  select * from tempa where
    tempb.di_id = tempa.di_id and
   and tempa.acct_number LIKE '%'||tempb.acct_number||'%'
);

Internet Services

Web Development

Digital Marketing

Consumer Tech