Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

*********************************************
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

 

Posted on Tuesday, July 14, 2009 4:50 PM Resume | Back to top

Related Posts on Geeks With Blogs Matching Categories

Comments on this post: Find Orphaned records - on n columns

# re: Find Orphaned records - on n columns
Requesting Gravatar...
I think I am not good enough for this soft. I can not understand it.wedding dresses
designer wedding dress
Left by GG on Mar 29, 2010 12:28 AM

# re: Find Orphaned records - on n columns
Requesting Gravatar...
Excellent post, reallly helpful as always.
Left by albert on Nov 04, 2011 7:43 AM

# re: Find Orphaned records - on n columns
Requesting Gravatar...
What a wonderful post, thank you so much for helping me with this topic.
Left by art on canvas on Nov 21, 2011 4:56 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net