Record Matching between two tables in oracle-CodePudding

I have below two table.

create table main_supplier(file_id number,process_id number,supplier_code
number);
create table addition_supplier(file_id number,process_id number,supplier_code
number);

insert into main_supplier values(1,2,4567);
insert into main_supplier values(1,2,1234);
insert into main_supplier values(1,2,5890);



insert into addition_supplier values(1,2,7890);
insert into addition_supplier values(1,2,1234);
insert into addition_supplier values(1,2,5890);

in the above table the supplier_code in main_supplier table should not exists in supplier_code in addition_supplier table.so i wrote one cursor in plsql block.

cursor c1 select ta.supplier_code
  from main_supplier ta
  where ta.file_id=1
  and ta.process_id= 2
  and  exists(select 1
                 from addition_supplier sa
                 where  sa.file_id=ta.file_id
                        and sa.process_id= ta.process_id 
                 and sa.supplier_code=ta.supplier_code);
open c1;
loop
 fetch c1 into a;
   if a is not null then
     raise error;
   end if;
end loop;

The above select query is correct?

CodePudding user response：

The query itself works. Don't realy know what do you want to do as your query's result is:

SUPPLIER_CODE
1234
5890

This is the result of EXISTS() condition - if that is what you are looking for then, yes, your sql is ok and you got the rows that exists in a table where they shouldn't - what you will do with them is up to you. Your (sample) PL/SQL code will raise an error at the first of the rows and exit. But this is just a sample hopefuly...

Test

WITH 
    main_supplier  (FILE_ID, PROCESS_ID, SUPPLIER_CODE)     AS
        (
            Select 1, 2, 4567 From Dual Union All
            Select 1, 2, 1234 From Dual Union All
            Select 1, 2, 5890 From Dual 
        ),
    addition_supplier (FILE_ID,PROCESS_ID,SUPPLIER_CODE)    AS
        (
            Select 1, 2, 7890 From Dual Union All
            Select 1, 2, 1234 From Dual Union All
            Select 1, 2, 5890 From Dual 
        )

    SELECT    ta.SUPPLIER_CODE
    FROM      main_supplier ta
    WHERE     ta.FILE_ID=1  and ta.PROCESS_ID = 2 and  
            Exists(Select   1
                   From     addition_supplier sa
                   Where    sa.FILE_ID = ta.FILE_ID And sa.PROCESS_ID = ta.PROCESS_ID  And sa.SUPPLIER_CODE = ta.SUPPLIER_CODE)

SUPPLIER_CODE
-------------
         1234 
         5890

The other way to get this result could be using INNER JOIN...

    SELECT    ta.SUPPLIER_CODE
    FROM      main_supplier ta
  INNER JOIN  addition_supplier sa 
              ON( sa.FILE_ID = ta.FILE_ID And sa.PROCESS_ID = ta.PROCESS_ID  And sa.SUPPLIER_CODE = ta.SUPPLIER_CODE)
    WHERE     ta.FILE_ID=1  and ta.PROCESS_ID = 2 

SUPPLIER_CODE
-------------
         1234 
         5890