Exception Query

  • Thread starter Thread starter operator
  • Start date Start date
O

operator

I have two tables, Project and Contact, with a one-to-many
relationship. The columns that link these tables are A and X,Y,Z
respectively. A, X, Y, and Z are of type Text. I want a query to return
all Project records that do not have a Contact record associated with
them. Here is what I thought would work:

select distinct Project.* from Project, Contact
where Project.A not like Contact.X and Project.A not like Contact.Y and
Project.A not like Contact.Z

This seems to return everything and I'm beginning to understand why. I
am at a loss of how to achieve my goal though. Any
thoughts/suggestions?

(Background info: There is currently a query that returns projects and
their associated contacts. However, projects that do not have contacts
are omitted and thus my need for this sort of "exception list".
 
Use this SQL statement/

SELECT PROJECT.A, CONTACT.X, CONTACT_1.Y, CONTACT_2.Z
FROM ((PROJECT LEFT JOIN CONTACT ON PROJECT.A = CONTACT.X) LEFT JOIN CONTACT
AS CONTACT_1 ON PROJECT.A = CONTACT_1.Y) LEFT JOIN CONTACT AS CONTACT_2 ON
PROJECT.A = CONTACT_2.Z
WHERE (((CONTACT.X) Is Null) AND ((CONTACT_1.Y) Is Null) AND ((CONTACT_2.Z)
Is Null));
 
Back
Top