--sample tables and sample data
Create Table #emp ( [PK] int not null,
[Name] char(20) not null)
Insert INTO #emp (PK,[NAME])
Select 1, 'Peter' UNION ALL
Select 2, 'Paul' UNION ALL
Select 3, 'Mary'
Create Table #train ( [PK] int not null,
[PK_emp] int not null,
[Category] char(20) not null,
[expiration] datetime null)
Insert Into #train (PK,PK_emp, Category, Expiration)
Select 1, 1, 'First Aid', '12/22/2007' UNION ALL
Select 2, 1, 'Other', NULL UNION ALL
Select 3, 2, 'First Aid', '12/22/2007' UNION ALL
Select 4, 2, 'Other', NULL UNION ALL
Select 5, 3, 'Other', NULL
--return all records from #emp, and category and training from expiration
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
FROM #emp left join #train on #emp.PK = #train.PK_emp
--return only one ROW per employee and either the expiration date if First Aid,
--or NULL if no entry for that employee, or with this sample, employees 1,3,5
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
FROM #emp left join #train on #emp.PK = #train.PK_emp
WHERE Category = 'First Aid'
--get all employees and either First Aid Expire or Other category with NULL
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
FROM #emp inner join #train on #emp.PK = #train.PK_emp
Where Category = 'First Aid' and Expiration is not null
UNION
Select #emp.PK, #emp.[name],#train.Category, #train.Expiration
FROM #emp inner join #train on #emp.PK = #train.PK_emp
Where #emp.PK not in (Select #emp.PK
FROM #emp inner join #train on #emp.PK = #train.PK_emp
Where Category = 'First Aid' and Expiration is not null)
Drop Table #emp
Drop Table #train
I see where you would have issues getting desired results.