CODEWITHSUNDEEP
sqlms-access
Alexander Cohen
Alexander Cohen

Reputation: 71

Multi Table Query Access Comparing Data

If I have three tables, one called Person, one called Owner and the other called Tenant. All three have SSN as one of the fields. What I want to do is compare the SSN from Person (that's the whole list) to see which ones do not show up in either OWner or Tenant so I can see which people in the database have never owned a unit or leased a unit. Then i would like to be able to delete these people out of the person table.

Thanks

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

One easy way to do this is using not in:

select p.*
from persons as p
where p.ssn not in (select ssn from owner) and
      p.ssn not in (select ssn from tenant);

Upvotes: 2

Related Questions