*********************************************
Find Orphaned records - on n columns
*********************************************

Example below has two tables: GrantProjectDates  & GrantProjectDollars

related key columns:  GrantId, CountryCode, ProjectId

The tables are loaded from teo excel worksheets , so there are lots of holes in the data,
so ading foreign keys on the related key columns is not helpful,
each table has an IdentiyColumn "id" 

*********************************************
Orphaned GrantProjectDates 
*********************************************

select * from GrantProjectDates where id in (
select id from (
select da.Id, da.GrantId, da.excelRow, da.CountryCode, da.ProjectId, do.GrantId, do.CountryCode, do.ProjectId
FROM GrantProjectDates da
left join GrantProjectDollars do
on da.GrantId = do.GrantId
and da.CountryCode = do.CountryCode
and da.ProjectId = do.ProjectId
where do.GrantId is null
or do.CountryCode is null
or do.ProjectId is null))
 and Obsolete = false
order by id

*********************************************
Conversely,  Orphaned GrantProjectDollars
*********************************************
select * from GrantProjectDollars where id in (
select id from (
select do.Id, do.GrantId, do.excelRow, do.CountryCode, do.ProjectId, da.GrantId, da.CountryCode, da.ProjectId
FROM GrantProjectDollars do
left join GrantProjectDates da
on do.GrantId = da.GrantId
and do.CountryCode = da.CountryCode
and do.ProjectId = da.ProjectId
where da.GrantId is null
or da.CountryCode is null
or da.ProjectId is null))
 and Obsolete = false