No Matching Subrecord

Finds all records in Table A that don't have a matching subrecord in Table B.  

       

SELECT a.* 
FROM <table a> AS a 
LEFT JOIN <table b> AS b 
ON a.<link field> = b.<link field> -- The standard join between A and B. 
AND b.<another field> = <value> -- All criteria for B must go here, NOT in the Where clause. 
WHERE a.<another field> = <value> -- All criteria for A goes here. 
AND b.<primary key> Is Null -- This is the ONLY criterion for B that goes in the Where clause.

It's important to note the placement of criteria for table B.  If you place any criteria in the Where clause other than the "primary key Is Null", no records of any sort will be returned.  But additional criteria can be included in the Join clause even if they have nothing to do with the join.

Example:

SELECT p.* 
FROM Contact AS p 
LEFT JOIN Task AS t 
ON p.ContactId = t.RegardingObjectId -- Standard join between Contact and Task: Tasks regarding that person 
AND t.a422_rel_activityvalueidname = 'Official Transcript' -- Criteria for Task, what you don't want the persons to have 
WHERE p.LastName = 'Jones' -- Criteria for Contact, what you do want the persons to have. 
AND t.ActivityId Is Null -- ActivityId can't be Null, so if it is, that means there's no matching Task.

       


Comments