Find out rows in one table but not the other 1

[pnad]

Hello,

I have two tables table A and table B.

The primary keys in the two tables are different.
The PK in table A is a combination of companycode, policynumber and secondarycompanycode and the PK in table B is claimantIDnumber. Table B has columns companycode, policynumber and secondarycompanycode too.

Now during an update operation into tableB, for a given ClaimantIdnumber, the companycode and policynumber were updated but not the secondarycompanycode.

I need to find out all the claimantIDs that have the same companycode and policynumber in both the tables but different secondarycompanycodes.

Any help will be appreciated.

Thanks.

 

[Dagon]

select * from t1 where exists
(select 1 from t2
where t1.companycode = t2.companycode
t1.policynumber = t2.policynumber
and t1.secondarycompanycode <> t2.secondarycompanycode)

 

[pnad]

Thanks Dagon for your quick response.

I had tried a similar query before and it gave me 74 rows. I am sure that there are more rows in table 2 (claimantrecord) with a different secondary company code.

I had tried

select b.companycode, b.policynumber, b.secondarycompanycode, a.secondarycompanycode, b.claimantidnumber
from ohco.policyrecord a, ohco.claimantrecord b
where a.companycode = b.companycode
and a.policynumber = b.policynumber
and a.secondarycompanycode <> b.secondarycompanycode

 

[Dagon]

The database doesn't lie generally, so that is probably the correct answer. The only situation it wouldn't find is if the secondarycompanycode is null in either table. You could allow for this:

select b.companycode, b.policynumber, b.secondarycompanycode, a.secondarycompanycode, b.claimantidnumber
from ohco.policyrecord a, ohco.claimantrecord b
where a.companycode = b.companycode
and a.policynumber = b.policynumber
and nvl(a.secondarycompanycode, 'X') <> nvl(b.secondarycompanycode, 'X')

You could also try a simple minus query.

select companycode, policynumber, secondarycompanycode
from claimantrecord
minus
select companycode, policynumber, secondarycompanycode
from policyrecord

 

[pnad]

The first query returns 250 rows and the minus returns 236 rows. Is that possible?

 

[Dagon]

Yes, mins suppresses the duplicates in the same way as UNION and INTERSECT.

 

[Dagon]

It's annoying you can't edit here. Minus suppresses the duplicates in the same way as UNION and INTERSECT.

 

[pnad]

Thank you for all your help.