Phew, I think I've got it (note the field names might be slightly different to the example I gave, sorry if that confuses anybody):

SELECT ped_pk
	  ,cov_empfk
	  ,emp_clifk
	  ,cov_pk 
  FROM [PlanEnrolmentTypeDocuments]
	inner join CoverageHistory a on cov_entfk = ped_entfk and ped_plnfk = (select cpl_plnfk from clientplans where cpl_pk = cov_cplfk)
	inner join employees on cov_empfk = emp_pk
	inner join enrolmenttypes on cov_entfk = ent_pk
where ped_pk not in
(SELECT edc_pedfk
  FROM [CoverageHistory]
	inner join ClientPlans on cov_cplfk = cpl_pk
	inner join employees on cov_empfk = emp_pk
	inner join EnrolDocuments on cov_pk = edc_covfk
	inner join PlanEnrolmentTypeDocuments on edc_pedfk = ped_pk
	where emp_pk = a.cov_empfk)

>Hi,
>
>Friday's are when I struggle the most to think clearly :(
>
>I have the following tables:
>
>Clients - list of clients
>cli_pk (primary key)
>cli_name
>
>Plans - a list of all plans
>pln_pk
>pln_name
>
>ClientPlans - list of plans (Insurance and pension) that a client belongs to
>cpl_pk
>cpl_clifk (foreign key to Clients table)
>cpl_plnfk (foreign key to Plans table)
>
>Employees - list of employees within clients
>emp_pk
>emp_clifk (foreign key to clients table)
>
>CoverageHistory - list of coverage history for each employee (records for enrolled, beneficiary changed, etc)
>cov_pk
>cov_ctyfk (foreign key to CoverageHistoryChangeTypes table)
>cov_empfk (foreign key to employees table)
>cov_cplfk (foreign key to client plans table)
>
>CoverageHistoryDocuments - list of supporting documents for each change in coverage
>chd_pk
>chd_covfk (foreign key to CoverageHistory table)
>chd_pcdfk (foreign key to PlanChangetypeDocuments)
>
>CoverageHistoryChangeTypes - a list of each possible type of coverage change
>cty_pk
>cty_name
>
>PlanChangetypeDocuments - a list of documents required for each plan and coverage history change type.
>pcd_pk
>pcd_plnfk (foreign key to Plans)
>pcd_ctyfk (foreign key to CoverageHistoryChangeTypes)
>pcd_document
>
>For some sample data:
>Clients
>1, Client1
>2, Client2
>
>Plans
>1, plan1
>2, plan 2
>
>ClientPlans
>cpl_pk, cpl_clifk, cpl_plnfk
>1, 1, 1
>2, 1, 2
>3, 2, 1
>
>Employees
>emp_pk, emp_clifk
>1, 1
>2, 1
>3, 2
>
>CoverageHistory
>cov_pk, cov_ctyfk, cov_empfk, cov_cplfk
>1, 1, 1, 1
>2, 2, 1, 1
>3, 1, 2, 1
>4, 1, 3, 3
>
>
>CoverageHistoryDocuments
>chd_pk, chd_covfk, chd_pcdfk
>1, 1, 1
>2, 2, 2
>
>
>CoverageHistoryChangeTypes - a list of each possible type of coverage change
>cty_pk, cty_name
>1, Enrolment
>2, Change of Beneficiary
>
>PlanChangetypeDocuments - a list of documents required for each plan and coverage history change type.
>pcd_pk, pcd_plnfk, pcd_ctyfk, pcd_document
>1, 1, 1, enrolment document
>2, 1, 1, id card
>3, 2, 1, enrolment document
>4, 1, 2, beneficiary statement
>
>I need to find what documents (if any) are missing for each coveragehistory record.
>
>In the above example (hopefully I've given a good example!) I should get a list like this:
>
>cov_pk, doc_pk, doc_name
>1, 2, id card (only the enrolment document was recorded but the id card should have been recorded too)
>3, 1, enrolment document
>3, 2, id card
>4, 3, enrolment document
>
>I hope I've explained this well.
>
>Any help?