I have two tables in a 1 to many rel. How do I find organizations with no
members? Thanks. Here's my two tables...
ORGANIZATION
OrgID (pk)
OrgName
MEMBERS
MemID (pk)
MemberName
OrgID (fk)
Use the Unmatched Query Wizard in query design, or - pretty much equivalently
- copy and paste this SQL into the SQL window of a new query:
SELECT OrgID, OrgName
FROM Organization LEFT JOIN Members
ON Members.OrgID = Organization.OrgID
WHERE Members.OrgID IS NULL;
This "frustrated outer join" will retrieve all organizations (because of the
left join); if there are matching records in Members it will retrieve the
OrgID from the Members table. If there is no matching record it will show
Members.OrgID as NULL - and the criterion IS NULL will select just those
records.
John W. Vinson [MVP]