CODEWITHSUNDEEP
sqlsql-serversql-server-2014
John Molina
John Molina

Reputation: 321

If not or if not exist

All theses tables ERTBatches, ERTBatchChecks, ERTClaims have an identifier.
Lets say the identifier is 1010.
This identifier must exist in all three tables.

I'm trying to find out if any identifer is not in all three tables.

These are the names of the Identifiers :

ERTBatches."BatchIdentifier"
ERTClaims."ERTBatchNumber"
ERTBatchChecks."BatchIdentifier"

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

One method is union all and aggregation:

select id
from ((select b.BatchIdentifier as id, 1 as which from ERTBatches b) union all
      (select bc.BatchIdentifier as id, 2 as which from ERTBatchChecks bc) union all
      (select c.ERTBatchNumber as id, 3 as which from ERTClaims c)
     ) t
group by id
having count(distinct which) < 3;

Upvotes: 5

Related Questions